SQLTeam.com | Weblogs | Forums

Trigger to update same table after updating a particular row

sql2008

#1

CREATE TRIGGER [dbo].[after_update] ON [dbo].[tbl_customer]
AFTER UPDATE AS
set nocount on
BEGIN
DECLARE

@unq varchar(20)
select @unq=updated.billno from updated

UPDATE tbl_customer SET certificatedate = GETDATE()WHERE [billno] = @unq

I want to update same table row's colum after an update statement is performed on that row


#2

So, does you trigger work? (Also, did you post the whole thing? I don't see an END statement)

FWIW, you should assume that the pseudo-table "updated" may contain more than one row. Your code would fail if two rows are updated.


#3

To add to what @gbritton said.

You might look into an instead of trigger.


#4

Don't get involved with INSTEAD OF triggers unless you absolutely have to:

CREATE TRIGGER [dbo].[after_update] 
ON [dbo].[tbl_customer]
AFTER UPDATE 
AS
SET NOCOUNT ON;
UPDATE c
SET certificatedate = GETDATE()
FROM dbo.tbl_customer c
INNER JOIN inserted i ON
    i.billno = c.billno
WHERE
    c.certificatedate IS NULL OR
    c.certificatedate < DATEADD(MINUTE, -10, GETDATE())
GO --end of trigger

#5

my trigger is not working and i want to update only 1 row at a time


#6

thank you ScottPletcher for the querry u solved my problem :relaxed:


#7

c.certificatedate < DATEADD(MINUTE, -10, GETDATE())
can you explain what is happening in the above line


#8

checking if certificatedate is more than ten minutes before the current date/time


#9

Trying to prevent UPDATEing the same row twice by a recursive trigger call. Probably safer to reduce it to 1 minute or 2; I was trying to allow for wait time for the UPDATE, but 10 minutes is almost certainly too long.


#10

Thank you very Much for explaining me. :relaxed:


#11

Beware that you need to write your Triggers so that they CAN handle multiple rows. (Either than or check the ROWCOUNT in the Inserted / Deleted pseudo table and if > 1 then raise an error and rollback the transaction - so that at least your trigger is "safe" in the event of a multi-row update / insert / delete.