Trigger to update same table after updating a particular row

Transact-SQL
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.