SQL Server After Insert and After Update trigger

Question

I need to create after insert and after update trigger. If the new row is inserted in table, it should run after insert trigger and put timestamp in other table. But if this row is edited again, it should update the other table also.

The status can be changed to active, pending etc. So when each status changes, I need to put timestamp. And for every new record I need to put new row.

Here is the table structure:

| ID | Name | Status |
|----|------|--------|
| 1  | xyz  | Active |
|    |      |        |
|    |      |        |

Let's say this is new row inserted in table, so it should be inserted into another table. But when I change its status, it should update the other table against this ID.

| ID | Name | Active Staus | Other Status |
|----|------|--------------|--------------|
| 1  | xyz  | TimeStamp    | Time Stamp   |
|    |      |              |              |
|    |      |              |              |`


 USE [DemoDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[test_INSERT]
       ON [dbo].[Demo]
AFTER INSERT
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE ID BIGINT
       Declare @status varchar(50)
       SELECT @ID = INSERTED.ID 

       FROM INSERTED

       INSERT INTO [dbo].[LogTble]
       VALUES(@ID,'timestamp')
END

So what have you written as your triggers so far?? Where are you stuck? — marc_s
i just did simple insert. i dont know how i can achieve that . — ROMA

marc_s marc_s · Accepted Answer · 2017-12-13T08:53:59

Well - your trigger is halfway there.

You're assuming that there's only one row in Inserted - this is NOT generally the case! If your INSERT statements inserts multiple rows, there will be multiple entries in Inserted and code like this:

  SELECT @ID = INSERTED.ID FROM INSERTED

will fail miserably.... you'll get one arbitary row selected - and all others are ignored....

You need to be aware that Inserted will contain multiple rows and you need to write your trigger accordingly - in a set-based fashion.

Try this:

ALTER TRIGGER [dbo].[test_INSERT]
ON [dbo].[Demo]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[LogTble] (Id, ActiveStatus)
        SELECT i.ID, SYSDATETIME()
        FROM Inserted i
END

SQL Server After Insert and After Update trigger

1 Answers