how to create such trigger

24 Aug 2006 1:39 PM

mamin

Hi,
I have a table TABLE1 with columns ID, NAME, DATE1
I need to write a trigger that will update DATE column to current date
time,
after every sql update operation on record.

So my trigger looks as follows:

declare
@row_id bigint
set @row_id=(select id from inserted)
update TABLE1 set DATE1=getdate() where id=@row_id

It works perfectly unless I use update query for more than one row in
the same time, for example:
update TABLE1 set NAME='aaa' where ID in (1,2)

The error message is
"Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= , >, >= or when the subquery is used as
an expression.
The statement has been terminated."

How to write a trigger that will work correctly for this situation?

Thanks,
mamin

24 Aug 2006 1:45 PM

Tracy McKibben

ma***@o2.pl wrote:
Show quote

> Hi,
> I have a table TABLE1 with columns ID, NAME, DATE1
> I need to write a trigger that will update DATE column to current date
> time,
> after every sql update operation on record.
>
> So my trigger looks as follows:
>
> declare
> @row_id bigint
> set @row_id=(select id from inserted)
> update TABLE1 set DATE1=getdate() where id=@row_id
>
> It works perfectly unless I use update query for more than one row in
> the same time, for example:
> update TABLE1 set NAME='aaa' where ID in (1,2)
>
> The error message is
> "Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as
> an expression.
> The statement has been terminated."
>
> How to write a trigger that will work correctly for this situation?
>
> Thanks,
> mamin
>

UPDATE Table1
SET Date1 = GETDATE()
WHERE id IN (SELECT id FROM inserted)

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

24 Aug 2006 1:56 PM

mamin

thanks!!!

24 Aug 2006 1:49 PM

ML

Triggers are fired per statement, not per row, so make sure your triggers
support multiple operations.

Try this (untested):

update TABLE1
set DATE1=getdate()
from TABLE1
inner join inserted
on inserted.id = TABLE1.id

ML

---
http://milambda.blogspot.com/