Update Trigger select current row fields 3

[DaveJohnson]

I have created the following update trigger which works, what need to do is set the SET @Project and @Value with the values of two fields in the PA01201 table.

CREATE TRIGGER [CheckUpdate] ON [dbo].[PA01201]
FOR UPDATE

AS
if update (SUTASTAT)
BEGIN

DECLARE @sys_usr char(30), @sys_timestamp datetime, @Project as char(15), @Value as char(3)
SET @sys_usr = SYSTEM_USER
SET @sys_timestamp = getdate()
SET @Project = '234567'
SET @Value = 'CO'

INSERT INTO Audit_PA01201_SUTASTAT VALUES (@Project, @sys_usr, @sys_timestamp, @Value, (newid()) )

END

Thanks

Dave Johnson.

 

[bborissov]

SELECT @Project = Inserted.ProjectField,
       @Value   = Inserted.ValueField
FROM Inserted

Keep in mind that the trigger is fired once anfetr whole update is finished. So you can have more than ONE row in Inserted table.


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[SQLSister]

A better way to handle it is using the select instead of values as the clause for the insert statement. Triggers should always be written to handle multiple record inserts even if you think that only single records will be inserted. Sooner or later someone will need to insert a group od records and then you lose the integrity of the data if your trigger is not correctly written. Note in my example I also specify the fields inteh insert. This is a good practice as people can actually change the order of the fields in Enterprise manager or through dropping and recreating the table and if they do, your trigger won't break.

CREATE TRIGGER [CheckUpdate] ON [dbo].[PA01201] 
FOR  UPDATE

AS
if update (SUTASTAT)
BEGIN

DECLARE @sys_usr char(30), @sys_timestamp datetime
SET @sys_usr = SYSTEM_USER
SET @sys_timestamp = getdate()


INSERT  INTO Audit_PA01201_SUTASTAT (Project, Userfield, datefield, valuefield, IDfield)
select Projectfield, @sys_usr,  @sys_timestamp, Valuefield ,newid() from inserted 

END

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[SQLDenis]

I would do it this way, why waist CPU cycles on variables

CREATE TRIGGER [CheckUpdate] ON [dbo].[PA01201] 
FOR  UPDATE

AS
IF @@ROWCOUNT =0
RETURN
if update (SUTASTAT)
BEGIN



INSERT  INTO Audit_PA01201_SUTASTAT (Project, Userfield, datefield, valuefield, IDfield)
select Projectfield, SYSTEM_USER,  getdate(), Valuefield ,newid() from inserted 

END

Also you should join inserted and deleted pseudo-tables because when you do something like this

update table
set value = 100 where value = 100

then the if update() will still fire but you value hasn't changed, to see what you have to do including checking for NULL values go here -->

http://sqlservercode.blogspot.com/2005/12/fun-with-sql-server-update-triggers.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/