AFTER UPDATE triggers in SQL Server

How to create AFTER UPDATE triggers in SQL Server?

AFTER UPDATE trigger is created in the same way as we created AFTER INSERT trigger, we can simply replace the INSERT word to UPDATE and change respective SQL statements.

CREATE TRIGGER [dbo].[UpdateAccounts]
       ON [dbo].[PersonalDetails]
       AFTER UPDATE
AS
BEGIN
       DECLARE @id int
       SELECT @id = PersonalDetailsId FROM inserted

       IF (NOT EXISTS(SELECT AutoId FROM Accounts
                            WHERE PersonalDetailsId = @id))
              BEGIN
                     INSERT INTO Accounts
                     (Salary, PPFDeduction, PersonalDetailsId)
                     VALUES
                     (0, 0, @id)
              END
END

(Few people use FOR instead of AFTER in the above query – notice the highlighted word. AFTER UPDATE or FOR UPDATE both are same.)

In the above query, we are creating a UpdateAccounts trigger that will execute only when PersonalDetails record will get updated.

In this trigger, we are checking if a corresponding record exists into the foreign key table Accounts for the current record that is being updated in PersonalDetails table. If corresponding record doesn’t exists into Accounts table, it adds a record with default value.

To trigger the UpdateAccounts trigger, just created we need to fire an UPDATE statement.