In this article I will explain with example, how to create Triggers in SQL Server that will insert data into another Table.
In some applications, we need to log the operations performed on tables and hence in such cases Triggers can be used to insert the records into the Log tables.
Database
I have made use of the following table Customers with the schema as follows.
![SQL Server: Insert data to another Table using Insert, Update and Delete Triggers]()
I have already inserted few records in the table.
![SQL Server: Insert data to another Table using Insert, Update and Delete Triggers]()
Below is the CustomerLogs table which will be used to log the Trigger actions.
![SQL Server: Insert data to another Table using Insert, Update and Delete Triggers]()
Note: You can download the database table SQL by clicking the download link below.
Triggers
Triggers are database operations which are automatically performed when an action such as Insert, Update or Delete is performed on a Table or a View in database.
Triggers are associated with the Table or View directly i.e. each table has its own Triggers.
Insert into another Table using Insert, Update and Delete Triggers
Now I will explain you with examples how to insert data into another Table using Insert, Update and Delete Triggers in SQL Server.
Insert data to another table in Insert Trigger
Below is an example of an After Insert Trigger. Whenever a row is inserted in the Customers Table, the following trigger will be executed. The newly inserted record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the inserted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_INSERT]
ON [dbo].[Customers]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = INSERTED.CustomerId
FROM INSERTED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Inserted')
END
Insert data to another table in Update Trigger
Below is an example of an After Update Trigger. Whenever a row is updated in the Customers Table, the following trigger will be executed. The updated record is available in the INSERTED table.
The following Trigger is fetching the CustomerId of the updated record. In order to find which column is updated, you will need to use UPDATE function and pass the Column name of the Table to it.
The UPDATE function will return TRUE for a Column if its value was updated else it will return false.
Finally based on which column of the record has been updated a record (containing the CustomerId and the appropriate action) is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_UPDATE]
ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
DECLARE @Action VARCHAR(50)
SELECT @CustomerId = INSERTED.CustomerId
FROM INSERTED
IF UPDATE(Name)
BEGIN
SET @Action = 'Updated Name'
END
IF UPDATE(Country)
BEGIN
SET @Action = 'Updated Country'
END
INSERT INTO CustomerLogs
VALUES(@CustomerId, @Action)
END
Insert data to another table in Delete Trigger
Below is an example of an After Delete Trigger. Whenever a row is deleted in the Customers Table, the following trigger will be executed. The deleted record is available in the DELETED table.
The following Trigger is fetching the CustomerId of the deleted record and the fetched value is inserted in the CustomerLogs table.
CREATE TRIGGER [dbo].[Customer_DELETE]
ON [dbo].[Customers]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CustomerId INT
SELECT @CustomerId = DELETED.CustomerId
FROM DELETED
INSERT INTO CustomerLogs
VALUES(@CustomerId, 'Deleted')
END
Screenshot
The following screenshot displays the Log table after the above Triggers were executed.
![SQL Server: Insert data to another Table using Insert, Update and Delete Triggers]()
