Trigger After Update , Insert from Inserted.

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

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger After Update , Insert from Inserted.

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-12-18 : 14:49:26

This trigger insert the row in another table when field2 is updated/changed.

when using this in sql server 2005 works, but someone tells me when using the sql server 2000 the trigger don't run. Is this code is supposed to work in both 2005/2000 or there is a problem with the sql server 2000 that I'm not aware of?. thanks.



alter TRIGGER xxx
on tableName

after update

as
begin

    set nocount on     

    if update(field2)

    begin

        insert into  tableName(field1,field2,field3)
        select field1,field2,field3
        from inserted
		
		if @@ERROR = 0 
				 begin
					commit transaction
				 end
			else
				begin
					rollback transaction
					
				end

    end

end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-19 : 06:48:15

It looks ok.
You may test it.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 06:52:23

Drop the complete IF @@error thingy.
The trigger execution is made in same context as original statement.

ALTER TRIGGER	xxx
ON		tableName
FOR		UPDATE
AS

SET NOCOUNT ON     

IF UPDATE(Field2)
	INSERT	tableName
		(
			field1,
			field2,
			field3
		)
        SELECT	field1,
		field2,
		field3
        FROM	inserted

E 12°55'05.63"
N 56°04'39.26"

CVDpr
Starting Member

41 Posts

Posted - 2008-12-19 : 08:56:52

the trigger still don't work in 2000, someone say to me that i can compare the values between the inserted and the field2. Any know about this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 09:03:44

Who says that?
Compare what?

E 12°55'05.63"
N 56°04'39.26"

CVDpr
Starting Member

41 Posts

Posted - 2008-12-19 : 09:18:07

quote:
Originally posted by Peso

Who says that?
Compare what?
E 12°55'05.63"
N 56°04'39.26"

That i have to use the inserted table and join with the updated table and compare then insert the values into the other table. :-(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-19 : 09:35:01

Compare what I ask?
You mean you want to log the records ONLY if there have been a change in field2?

-- Create log table
CREATE TABLE	tblLog
		(
			i INT,
			j INT
		)

-- Create work table
CREATE TABLE	tblPeso
		(
			i INT,
			j INT
		)

-- Create logging trigger
GO
CREATE TRIGGER	trgPeso
ON		tblPeso
FOR UPDATE
AS

IF UPDATE(j)
	INSERT	tblLog
	SELECT	i,
		j
	FROM	inserted
GO

-- Insert initial values in working table
INSERT	tblPeso
SELECT	1, 2 UNION ALL
SELECT	2, 3

-- Check log table
SELECT	*
FROM	tblLog

-- Update one record in working table
UPDATE	tblPeso
SET	j = 1
WHERE	i = 1

-- Check log table
SELECT	*
FROM	tblLog

-- Clean up
DROP TABLE	tblLog,
		tblPeso

E 12°55'05.63"
N 56°04'39.26"

daipayan
Posting Yak Master

181 Posts

Posted - 2009-03-12 : 18:13:05

THANKS PESO....YOUR TRIGGER HELPED ME A LOT!

quote:

Originally posted by Peso

Compare what I ask?
You mean you want to log the records ONLY if there have been a change in field2?

-- Create log table
CREATE TABLE	tblLog
		(
			i INT,
			j INT
		)

-- Create work table
CREATE TABLE	tblPeso
		(
			i INT,
			j INT
		)

-- Create logging trigger
GO
CREATE TRIGGER	trgPeso
ON		tblPeso
FOR UPDATE
AS

IF UPDATE(j)
	INSERT	tblLog
	SELECT	i,
		j
	FROM	inserted
GO

-- Insert initial values in working table
INSERT	tblPeso
SELECT	1, 2 UNION ALL
SELECT	2, 3

-- Check log table
SELECT	*
FROM	tblLog

-- Update one record in working table
UPDATE	tblPeso
SET	j = 1
WHERE	i = 1

-- Check log table
SELECT	*
FROM	tblLog

-- Clean up
DROP TABLE	tblLog,
		tblPeso

E 12°55'05.63"
N 56°04'39.26"

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources