Hi,

I'm trying to create a simple trigger in sequel server.  However i
don't know how to specify a field value in the row which has been
updated.
Hope this makes some sense ...

CREATE TRIGGER My_trigger
ON table1
FOR UPDATE
AS
IF UPDATE(field1)
UPDATE table2 SET  field1 = DATEADD(Hour,-1,GETDATE()) WHERE field1 =
(*********a field in the updated row*****)



Cheers
Mark

1. Triggers are fired per DML not per Row, you have to take care of that in
your code if you wanna do something with the resultsets.

2. IF UPDATE(field1)
   UPDATE table2 SET  field1 = DATEADD(Hour,-1,GETDATE()) WHERE field1 =
   FROM table2 t2
    inner join INSERTED I ON
    t2.idfield = i.idfield

You have to find the appropiate row for every row which is updated in your
talble, so the idcol is just a guess what might be to join.

HTH, Jens Suessmeyer.


<slinkyeleph***@hotmail.com> wrote in message
Show quoteHide quote

In a trigger you can reference two virtual tables called DELETED and
INSERTED. These contain the before and after states of the changed row(s).
Always remember that a trigger fires per statement not per row. That means
the DELETED/INSERTED tables may contain more than one row and you should
keep that in mind when you design your trigger code.

CREATE TRIGGER trg_some_table_update
ON some_table
FOR UPDATE
AS

UPDATE some_table
  SET some_col = DATEADD(HOUR,-1,CURRENT_TIMESTAMP)
  WHERE EXISTS
   (SELECT *
    FROM Inserted
    WHERE key_col = some_table.key_col) ;

GO

Now, if all you want is to default a column to the current time you could do
that with a DEFAULT constraint. If you want to have a column default to some
complex expression then just put that in the procedure that performs your
UPDATEs (you do of course perform all your data access operations through
stored procedures don't you?). Where triggers are really useful is for
fundamental business rules that will apply to or can be affected by
different operations - that is, more than one procedure or process perfoming
updates against the table.

ordered insert - select is not inserting in order
Delete Without Writing To Transaction Log
clustered index on a identity field
ROWS with null values getting truncated.
Dynamic order by case expression problem