triggers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

triggers

Author

Topic

jlbantang
Starting Member

16 Posts

Posted - 2007-07-28 : 00:57:27

im using fk field where the pk reside from another database. i need to know where i am going to place the trigger cmd. whenever an update successfully execute in pk table it should also reflect to db (another database) with fk field. do i need to create parameterized trigger?

a sample cmd with comments will help me a lot.

[url=http://www.cjb.net/images.html?83d04.gif]

[/url]

here is the code i tried:

CREATE TRIGGER tgr_Upddb2
@new_fkfld nvarchar(10)
ON db2
for UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for trigger here
update db2 set tb1.fk_fld=tb1.pk_fld
where fk_fld=@new_fkfld
END
GO

Kristen
Test

22859 Posts

Posted - 2007-07-28 : 02:53:00

Something like this maybe?


USE db1
GO
CREATE TRIGGER tgr_Upddb2
ON tbl1
FOR UPDATE
AS 
BEGIN
	SET NOCOUNT ON

	UPDATE U
	SET  tb1.Col1 = tb1.Col1,
		 tb1.Col2 = tb1.Col2,
		 ...
	FROM inserted AS I
		 JOIN db2.dbo.tb1 AS U
			 ON U.fk_fld = I.pk_fld
END
GO

Kristen

jlbantang
Starting Member

16 Posts

Posted - 2007-07-28 : 03:31:40

@k tnx. where im going to place the cmd? in db1.tb1 or db2.tb2?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-28 : 11:25:15

(1) You create a trigger on a TABLE
(2) You create it on the table which you want to monitor for any INSERT/UPDATE/DELETEs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jlbantang
Starting Member

16 Posts

Posted - 2007-07-29 : 01:29:30

can you please give me a sample code using the ff db

[url=http://www.cjb.net/images.html?46d45.jpg]

[/url]

im having hard time executing kristen code.

i tried executing the trigger

USE [testdb1]
GO
/****** Object: Trigger [dbo].[tgr_Upddb2] Script Date: 07/29/2007 08:34:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tgr_Upddb2]
ON [dbo].[Table1]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE U
SET Table1.pk_fld = Table1.pk_fld
FROM inserted AS I
JOIN testdb2.dbo.table2 AS U
ON U.fk_fld = I.pk_fld
END

ERR RETURN:
Msg 4104, Level 16, State 1, Procedure tgr_Upddb2, Line 8
The multi-part identifier "dbo.Table1.pk_fld" could not be bound.

btw i created the TRIGGER in testdb1.table1

Kristen
Test

22859 Posts

Posted - 2007-07-29 : 01:32:27

"where im going to place the cmd? in db1.tb1 or db2.tb2?"

In the database containing the table which will be modified

Kristen

Kristen
Test

22859 Posts

Posted - 2007-07-29 : 01:56:35

"dbo.Table1.pk_fld" could not be bound.

You cannot reference TABLE1 because it is not referenced in the UPDATE statement.

Perhaps you mean

SET U.pk_fld = I.pk_fld

??

Kristen

jlbantang
Starting Member

16 Posts

Posted - 2007-07-29 : 03:19:59

can you please rewrite the complete code using the database img above. tnx

jlbantang
Starting Member

16 Posts

Posted - 2007-07-29 : 03:48:43

update did not cascade to other db using this cmd:

USE [testdb1]
GO
/****** Object: Trigger [dbo].[tgr_Upddb2] Script Date: 07/29/2007 08:34:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tgr_Upddb2]
ON [dbo].[Table1]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON

UPDATE U
SET u.fk_fld = i.pk_fld
FROM inserted AS I
JOIN testdb2.dbo.table2 AS U
ON U.fk_fld = I.pk_fld
END

Kristen
Test

22859 Posts

Posted - 2007-07-29 : 05:26:31

You are setting it to the value it already is!!

SET u.fk_fld = i.pk_fld

is setting the same value as the constraint:

ON U.fk_fld = I.pk_fld

Kristen

jlbantang
Starting Member

16 Posts

Posted - 2007-07-29 : 05:35:37

can you please reconstruct the code. it will really help

Kristen
Test

22859 Posts

Posted - 2007-07-29 : 06:31:09

You'll need to explain the DEFINITION of what you want to do, and not your proposed solution.

And IMHO unless you can understand the resultant proposed solution its going to be a bit pointless because you won't be able to safely implement, debug & test or maintain the solution ...

Kristen

jlbantang
Starting Member

16 Posts

Posted - 2007-07-29 : 08:12:52

supposed a row update (pk_fld) in dbtest1-table1 was made i also want to reflect changes to the fk_fld filed which is in another database.

i hope these help

[url=http://www.cjb.net/images.html?ce7b4.gif]

[/url]

Kristen
Test

22859 Posts

Posted - 2007-07-30 : 03:23:42

[code]
UPDATE U
SET U.fk_fld = I.pk_fld
FROM inserted AS I
JOIN deleted AS D
ON D.pk_fld = I.pk_fld
JOIN testdb2.dbo.table2 AS U
ON U.fk_fld = D.pk_fld
[/code]
Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources