Trigger to insert data in another table in SQL Server

In this SQL Server tutorial, we will learn and comprehend How to create a trigger to insert data in another table in SQL Server. We will also discuss and learn several instances to assist you in better understanding the concept. The full list of topics we will cover is given below.

• Trigger to insert data in another table in SQL Server
• Trigger insert into another table SQL Server
• SQL Trigger To Update Another Table
• SQL Server Trigger After Insert, Update Specific Column
• SQL Server Trigger After Insert, Update, Delete

Also, check the related post: SQL Server Update Trigger Only If Column is Modified

Trigger to insert data in another table in SQL Server

Here we will learn and understand how to use the SQL Server AFTER trigger to insert data into another table by the query. And which will be explained with the help of an illustrated example.

When an action, like an Insert, Update or Delete is performed on a Table or a View in a database, a trigger is a database operation that is automatically carried out.
Each table has its triggers because they are directly related to the table or view.

In the INSERTED pseudo table, when we use the INSERT INTO statement to insert a new record into the table. A newly inserted record will be also available in the INSERTED table.

The SQL Server ORDER BY clause is used to arrange the result set records in the ascending or descending order by using the ASC or DESC keyword on the column_name or expression. If we don’t use the ORDER BY clause without any keyword then it will automatically arrange the records in ascending order.

EXAMPLE:

USE SQLMSERVERGUIDES;

CREATE TRIGGER dbo.after_usastates
on dbo.USA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
ORDER BY STATE_ID ASC;

INSERT INTO USA_STATES
VALUES(29,'North Dakota',21654,'Thor Odinson','Male');

In this preceding query, the CREATE TRIGGER statement is used to create a trigger called AFTER_USASTATES on the USA_STATES table by using the schema_name as dba. Then the AFTER trigger is used on the DML STATEMENT which means that once the AFTER trigger is fired then the DML STATEMENT will be triggered then the SQL STATEMENT will be executed after.

In the INSERT INTO statement, it will insert a new record into the USA_STATES table after the AFTER trigger is fired. A new record will also be inserted and available to the INSERTED pseudo table.

In the AS clause, the SELECT statement retrieves all records from the INSERTED pseudo table which is used with the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the ASC keyword which means that it will arrange the records in ascending order.

We hope that you have understood the subtopic “Trigger to insert data in another table in SQL Server” by using the SQL Server AFTER trigger on another table by the query. For a better description, we used an illustration and clarified it in depth.

Also, read: SQL Server Create Trigger If Not Exists

Trigger insert into another table SQL Server

We will learn and understand the subtopic “Trigger insert into another table SQL Server” by using the SQL Server AFTER trigger by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLMSERVERGUIDES;

CREATE TRIGGER dbo.after_canadastates
on dbo.CANADA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
ORDER BY STATE_ID DESC;

INSERT INTO CANADA_STATES
VALUES(31,'Michael Jordan','Yukon',54174);

In this aforementioned query, a trigger is created called AFTER_CANADASTATES on the CANADA_STATES table by using the CREATE TRIGGER statement. And we have used the schema_name as dba from the SQL Server. The DML STATEMENT will then be performed once the AFTER trigger has fired, as we have done by using the AFTER trigger on the DML STATEMENT. The SQL STATEMENT used within the AS clause will then be performed later.

In the INSERT INTO statement, we will insert a new record into the CANADA_STATES table after the AFTER trigger is fired. A new record will be inserted and available to the INSERTED pseudo table.

In the AS clause, we have used the SELECT statement to retrieve all records from the INSERTED table based on the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword which means that it will arrange the records in descending order from the CANADA_STATES table.

If the records are arranged in descending order then the SELECT statement will retrieve all records from the CANADA_STATES table.

We hope that you have understood how to use the SQL Server AFTER trigger on the INSERTED table by the query. We have used an example and explained it in depth, for a better understanding.

Read: SQL Server Trigger Before Insert

SQL Server Trigger To Update Another Table

In this SQL Server section, we will learn and understand how to use the SQL Server UPDATE trigger to insert records into another table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER dbo.after_updateinserted
on dbo.CANADA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
ORDER BY STATE_ID DESC;

UPDATE CANADA_STATES
SET CANADA_STATENAME='Yukon'
WHERE STATE_ID=27;

Using the CREATE TRIGGER statement, we have built a trigger on the CANADA_STATES table with the name AFTER_UPDATEINSERTED. The DML STATEMENT will then be triggered once the AFTER trigger has fired, as we have used the AFTER trigger on the DML STATEMENT. This is followed by the execution of the SQL STATEMENT included within the CREATE TRIGGER statement.

In the UPDATE statement, it is used to update and set a new string_value of the CANADA_STATENAME column as Yukon based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 27 from the CANADA_STATES table.

The UPDATE statement will return TRUE for a column only when it is updated else it will return a FALSE value.

In the AS clause, we have used the SELECT statement will retrieve all records from the INSERTED pseudo table based on the ORDER BY clause. In the ORDER BY clause, the STATE_ID column is used with the DESC keyword which means that it will arrange the records for the result set in descending order. Once the records are arranged in descending order then the SELECT statement will retrieve all records from the INSERTED pseudo table.

We hope that you have understood the subtopic “SQL Trigger To Update Another Table” by using the SQL Server UPDATE trigger on another table by the query. For a better acquaintance, we have used an instance and described it in depth.

Read; SQL Server Trigger After Insert Update

SQL Server Trigger After Insert, Update Specific Column

Here we will learn and understand how to use the SQL Server AFTER trigger with the INSERT and UPDATE statement on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER afterupdatecolumn
ON USA_STATES
AFTER INSERT, UPDATE
AS
IF ( UPDATE (STATE_NAME) )
BEGIN
UPDATE USA_STATES
SET STATE_NAME='Ohio'
WHERE STATE_ID IN (SELECT DISTINCT STATE_ID FROM inserted)
END;

INSERT INTO USA_STATES(STATE_ID,STATE_ZIPCODE,FULL_NAME,GENDER)
VALUES(30,54120,'Sibelle Rigolle','Female');

SELECT * FROM USA_STATES 
WHERE STATE_ID>=28;

In this above query, we have created a trigger called AFTERUPDATECOLUMN on the USA_STATES table by using the CREATE TRIGGER statement. The AFTER trigger was then utilized on the INSERT and UPDATE statement, which means that the DML STATEMENT will be triggered as soon as the AFTER trigger fires.

Therefore, the SQL STATEMENT used inside the CREATE TRIGGER statement will be performed once the DML STATEMENT has been run and has had an impact on the table.

In the INSERT INTO statement, it will insert a new record into the USA_STATES table after the trigger is fired. But it has updated the STATE_NAME column as Ohio in the USA_STATES table record.

in the AS clause, we have used the UPDATE function to update the STATE_NAME column in the USA_STATES table. In the BEGIN statement, we have used the UPDATE statement to update and set a new string_value of the STATE_NAME column as Ohio which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the IN condition.

This means that the SELECT statement is used with the DISTINCT function in the INSERTED table. The DISTINCT function is used on the STATE_ID column to find unique values from the INSERTED pseudo table. To end the query, we have used the END statement.

This query means that when a new record will be inserted into the USA_STATES table, it will automatically update the STATE_NAME column as Ohio in the USA_STATES table. And it will also be available in the INSERTED pseudo table.

In the last query, we used the SELECT statement to retrieve all records from the USA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 28 from the USA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the USA_STATES table. But if the SELECT statement retrieves an empty record set from the USA_STATES table then the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server Trigger After Insert, Update Specific Column” by using the SQL Server AFTER trigger on the table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server Drop Trigger If Exists

SQL Server Trigger After Insert, Update, Delete

In this SQL Server section, we will learn and understand how to use the SQL Server AFTER Trigger used with the DML STATEMENTS on the table by the query. And which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_DMLSTATEMENT
ON CANADA_STATES
AFTER INSERT, DELETE, UPDATE
AS 
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=20;

INSERT INTO CANADA_STATES
VALUES(30,'Jim Kenndy','British Columbia',2117);

UPDATE CANADA_STATES
SET CANADA_STATENAME='Manitoba'
WHERE STATE_ID=28;

DELETE FROM CANADA_STATES
WHERE STATE_ZIPCODE=65214;

As we see in the above query, a trigger is created called AFTER_DMLSTATEMENT on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we used the AFTER trigger on the DML STATEMENTS which means that it will execute the statement first as the trigger is fired. Then the SQL STATEMENT which is used inside the AS clause will be executed after the CREATE TRIGGER statement is triggered.

Here is the execution of the DML STATEMENT which will be executed once the AFTER trigger is fired. Here is the execution of the DML STATEMENT which says:

• In the INSERT INTO statement, we have to insert a new record into the CANADA_STATES table as the AFTER trigger is fired.
• In the UPDATE statement, it will update and set a new string_name as Manitoba from the CANADA_STATES table which is based on the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 28 from the CANADA_STATES table.
  • if the WHERE condition, the UPDATE statement is available to update and set a string_value only when the WHERE condition turns out to be TRUE.
  • But if the WHERE condition gets a FALSE value then the UPDATE statement will update no new value for that column and the query will be executed successfully.
• In the DELETE statement, it will delete one record from the CANADA_STATES table which is used with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 24 from the CANADA_STATES table.
  • If the WHERE condition gets a FALSE value then the DELETE statement will not delete one record from the CANADA_STATES table only the query will execute successfully.
  • If the DELETE statement can delete one record from the CANADA_STATES table only when the WHERE condition turns out to be TRUE.

In the AS clause, the SELECT statement retrieves all records from the CANADA_STATES table which is used with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 20 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the CANADA_STATES table. If the SELECT statement retrieves an empty record set from the CANADA_STATES table only when the WHERE condition turns out to be a FALSE value.

We hope that you have understood the subtopic “SQL Server Trigger After Insert, Update, Delete” by using the SQL Server AFTER trigger on the table by the query. For a better understanding, we have used an example and explained it in depth.

Also, take a look at some more SQL Server tutorials.

• Instead of Trigger In SQL Server
• Introduction to SQL Server Trigger
• SQL Server User Permissions
• SQL Server Row_Number Join
• Drop stored procedure SQL Server
• SQL Server Trigger If Exists Raise Error
• SQL Server Trigger to check if value has changed
• SQL Server Trigger on Delete Insert Into Another Table

With this SQLServer tutorial, we now know Trigger to insert data in another table in the SQL Server statement after reading this lesson. We also discussed a few instances to help you comprehend the concept. Below is a list of all the topics we’ve covered.

• Trigger to insert data in another table in SQL Server
• Trigger insert into another table SQL Server
• SQL Server Trigger To Update Another Table
• SQL Server Trigger After Insert, Update Specific Column
• SQL Server Trigger After Insert, Update, Delete