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.
Views: 1945 | Post Order: 113