---

Search posts under SQL Server:

---

AFTER INSERT triggers in SQL Server

How to create AFTER INSERT triggers in SQL Server?

AFTER INSERT trigger executes after a record is inserted into the database. Open a new query window and write below statements

CREATE TRIGGER InsertAccounts
       ON PersonalDetails
       AFTER INSERT
AS
BEGIN
       DECLARE @id int
       SELECT @id = PersonalDetailsId FROM inserted

       INSERT INTO Accounts
       (Salary, PPFDeduction, PersonalDetailsId)
       VALUES
       (0, 0, @id)
END

Notice the above code snippet, the name of the trigger is InsertAccounts and created on PersonalDetails table. The type of this trigger is “AFTER INSERT”.

Inside the trigger, we have declared a variable and trying to get the PersonalDetailsId primary key value of the inserted table (in this case PersonalDetials – the highlighted word “inserted” is a logical table that gets created with the record data that was inserted, the scope of this table is limited to the scope of the trigger) and the same is being used to insert a record into Accounts table as foreign key and default values of Salary and PPFDeduction.

To test, whether this Trigger is working, try to insert a new record into PersonalDetails table.

INSERT INTO PersonalDetails
VALUES
('T FirstName 1 ', 'TLastName 1 ', 31, 1)

This insert a record into PersonalDetails table and a record into Accounts table (two records).

Result Window

How to delete a trigger?

To delete any trigger created, simply right click the trigger name and choose Delete and click OK on the dialog box. It can also be done using DROP TRIGGER <trigger name> statement running in the query window.