Update trigger - update a field in the same row

Question

Update trigger - update a field in the same row

Terry-380449

Default port

Points: 1458
More actions
August 9, 2007 at 3:32 am

#180261

Hi!
I'm writing a simple update trigger, whenever one field changes, i update a date, et change back the value of that field.
What i need, is how to tell that the fiels date is the one of the same row where i am! I found that i can use "updated" or "inserted", can you help out?
CREATE TRIGGER myTrigger ON myTable
AFTER
UPDATE
as
BEGIN
SET NOCOUNT ON;
if UPDATE(field1)
begin
    if field1 = 1 ----> is this the new value after update?
    begin
        Update myTable set field2= getDate(),
                                    field1
= 0 ----> is this ok? to reset the field to its old value
        where myTable.key = ?????????????
             or use smthg like:
        FROM updated / inserted ???
        WHERE inserted.key = myTable.key ???
    end
end
END

thanks !!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Hi,

In SQL Server there are two tables that will help you inside a trigger: deleted and updated. There is no updated table, but you can see what has been updated, since these rows will be in both the inserted and deleted "tables".

In order to check the value of a particular column you will need to query either the deleted (for old values) or the inserted (for new values) tables.

You can set the field back to its old value. Whether this update will result in executing the trigger once again depends on your database option "recursive_triggers"

To see if this option is set see sp_dboption. To disable run:

exec 'databasename', 'recursive_triggers', false

This is the default. If recursive triggers are set you run the danger of an infinite call (which last 32 invocations, after that SQL Server will abort your trigger).

for updating the columns you can use something like:

update myTable set column1=getDate(), column2=1 where columnKey in (select columnKey from inserted)

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Terry-380449 Default port Points: 1458 More actions · Answer 2

Ok, deleted and inserted, not updated

That worked well

Thanks for the help!