SQL Server Trigger on Delete Insert Into Another Table

In this SQL Server tutorial, we will understand the implementation of SQL Server Trigger on Delete Insert into Another Table. In order to help you better understand the topic, we will talk about and learn from several examples. The whole list of subjects we’ll discuss is provided below.

• SQL Server Trigger on Delete Insert Into Another Table
• SQL Trigger Delete Record Another Table
• SQL Server Trigger Deleted Row
• Inserted and Deleted Tables in SQL Server Trigger
• Update Another Table Using Trigger SQL Server
• SQL Server Trigger to Update Another Table
• SQL Server Delete And Insert Into Another Table

Also, check the latest SQL Server tutorial: SQL Server Trigger to Increment Id

SQL Server Trigger on Delete Insert Into Another Table

We will learn and comprehend how to use the SQL Server AFTER trigger in this SQL Server part along with the query’s DELETE and INSERT statements on a different table and that will be clarified with the aid of an example.

EXAMPLE OF AFTER TRIGGER ON INSERTED TABLE:

USE sqlserverguides;

CREATE TRIGGER AFTER_ANOTHERTABLE
ON CANADA_STATES
FOR INSERT
AS 
SELECT * FROM INSERTED
WHERE STATE_ID>34;

INSERT INTO CANADA_STATES
VALUES(35,'Ava Addams','Quebec',45112);

In the first query, a trigger is created called AFTER_ANOTHERTABLE on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the FOR trigger on the INSERT statement which means that once the FOR trigger is fired then the DML STATEMENT will be triggered. Then the SQL STATEMENT will be executed which is used inside the CREATE TRIGGER statement.

In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table after the FOR trigger is fired. The same record will be available to the INSERTED pseudo table.

In the AS clause, the SELECT statement will retrieve all records from the INSERTED table used with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN operator to find a value greater than 34 from the INSERTED table. If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve records from the INSERTED table otherwise vice-versa.

EXAMPLE OF FOR DELETE TRIGGER ON DELETED TABLE:

USE sqlserverguides;

CREATE TRIGGER FOR_DELETEDTABLE
ON CANADA_STATES
FOR DELETE
AS 
SELECT * FROM DELETED
WHERE CANADA_STATENAME='Ontario';

DELETE FROM CANADA_STATES 
WHERE CANADA_STATENAME='Ontario';

In this preceding query, a trigger is created called FOR_DELETEDTABLE on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we used the FOR trigger on the DELETE statement which means that once the FOR trigger is fired then the DELETE statement will be triggered and executed on the CANADA_STATES table.

In the DELETE statement, it will delete the record from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the CANADA_STATENAME column is used with the EQUAL TO operator to find a string_name as Ontario from the CANADA_STATES table.

If the WHERE condition gets a TRUE value then the DELETE statement will delete the record from the CANADA_STATES table. But if the DELETE statement gets executed and doesn’t delete any record from the CANADA_STATES table only when the WHERE condition gets a FALSE value.

In the AS clause, the SELECT statement retrieves all records from the DELETED pseudo table based on the WHERE condition. In the WHERE condition, the CANADA_STATENAME column is used with the EQUAL TO operator to find a string_name as Ontario from the DELETED table.

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

This whole query means that once the AFTER trigger is fired then the DELETE statement will delete the record from the CANADA_STATES table and all deleted records will be available to the DELETED pseudo table.

We hope that you have understood how to use the SQL Server FOR trigger used with the INSERT and DELETE statement on the INSERTED and DELETED pseudo table by the query. For a better explanation, we have used an example and explained it in depth.

Read: SQL Server Trigger Before Update

SQL Trigger Delete Record Another Table

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_DELETED
ON HARVARD_UNIVERSITY
AFTER DELETE
AS 
SELECT * FROM DELETED
WHERE STUDENT_ID>14;

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_FIRSTNAME='Robert';

In this aforementioned query, a trigger is created called AFTER_DELETED on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. Then we fired the AFTER DELETE trigger which means that the DML STATEMENT will be triggered and after that, the SQL STATEMENT which is used inside the AS clause will be executed.

In the DELETE statement, it will delete one record from the HARVARD_UNIVERSITY table with the WHERE condition. In the WHERE condition, the STUDENT_FIRSTNAME column is used with the EQUAL TO operator to find a value equal to Robert from the HARVARD_UNIVERSITY table.

If the WHERE condition gets a TRUE value then it will delete the record from the HARVARD_UNIVERSITY table otherwise vice-versa.

In the AS clause, the SELECT statement retrieves all records from the DELETED table used with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN operator to find a value greater than 14 from the DELETED pseudo table. If the WHERE condition gets TRUE then the SELECT statement retrieves it otherwise the query will be executed.

Here is one thing to remember, if the AFTER trigger is fired on another table with the help of the DELETE statement then it will delete one record from the assigned table as the HARVARD_UNIVERSITY table. But it will insert the deleted record into the DELETED pseudo table.

We hope that you have understood the subtopic “SQL Trigger Delete Record Another Table” by using the SQL Server AFTER trigger on the DELETED table by the query. For a better exposition, we have used a sample and defined it in depth.

Read: SQL Server Trigger Before Insert

SQL Server Trigger Deleted Row

Here, we’ll go through how to use the SQL Server AFTER trigger on a DELETE statement to retrieve deleted rows for a query using the DELETED table and that will be clarified with the aid of an example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_DELETEDMIT
ON MIT_UNIVERSITY
AFTER DELETE
AS 
SELECT * FROM DELETED
WHERE STUDENT_ID=1001;

DELETE FROM MIT_UNIVERSITY
WHERE STUDENT_LASTNAME='Hemsworth';

in this aforementioned query, a trigger is created called AFTER_DELETEMIT on the MIT_UNIVERSITY table by using the CREATE TRIGGER statement. Then we fired the AFTER DELETE trigger on the DELETED table which means that the DML STATEMENT will be triggered and the SQL STATEMENT will be executed after that.

So, it means that the record which is deleted from the MIT_UNIVERSITY table will be inserted into the DELETED pseudo table by using the DELETE statement.

The DELETE statement will delete the record from the MIT_UNIVERSITY table which is based on the WHERE condition. In the WHERE condition, the STUDENT_LASTNAME column is used with the EQUAL TO operator to find a value equal to Hemsworth from the MIT_UNIVERSITY table.

If the WHERE condition turns out to be TRUE then the DELETE statement will delete records from the MIT_UNIVERISTY table otherwise vice-versa.

In the AS clause, the SELECT statement will retrieve all records from the DELETED table used with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find a value equal to 1001 from the DELETED table.

If the WHERE condition gets a FALSE value then the SELECT statement will be executed and will not be able to retrieve any record from the DELETED table. But if the SELECT statement retrieves all records from the DELETED table if the WHERE condition gets a TRUE value.

We hope that you have comprehended the subtopic “SQL Server Trigger Deleted Row” by using the SQL Server AFTER trigger on the DELETED pseudo table. We have used a sample and presented it in depth, for better performance.

Read: Disable Trigger in SQL Server

Inserted and Deleted Tables in SQL Server Trigger

In this SQL Server subtopic tutorial, we will learn and understand how to use SQL Server AFTER trigger with the INSERT and DELETE statement on the INSERTED and DELETED tables by the query. And which will be explained with the help of an illustrated example.

EXAMPLE OF INSERT TRIGGER ON INSERTED TABLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_INSERTED
ON CANADA_STATES
AFTER INSERT
AS 
SELECT * FROM INSERTED
WHERE STATE_ID>=28;

INSERT INTO CANADA_STATES
VALUES(34,'Barak Obama','Yukente',5124);

As we see in the above query, we have created a trigger called AFTER_INSERTED on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER INSERT trigger on the INSERTED table which means that once the AFTER trigger is fired then the DML STATEMENT will be triggered on the USA_STATES table. Then the SQL STATEMENT which is used inside the CREATE TRIGGER statement will be executed.

In the INSERT INTO statement, it will insert a new record into the CANADA_STATES table once the AFTER trigger is fired. The new record will be added to the CANADA_STATES table and also available in 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 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 INSERTED table.

If the SELECT statement retrieves all records from the INSERTED table only when the WHERE condition turns out to be TRUE.

EXAMPLE OF DELETE TRIGGER ON DELETED TABLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_DELETED
ON CANADA_STATES
AFTER DELETE
AS 
SELECT * FROM DELETED
WHERE STATE_ID>28;

DELETE FROM CANADA_STATES
WHERE STATE_ID=30;

As we see in the above query, a trigger is created called AFTER_DELETED from the CANADA_STATES table by using the CREATE TRIGGER statement. Then, we applied the AFTER trigger to the DELETE statement, meaning that the DML STATEMENT would be triggered as soon as the AFTER trigger fires. Following that, the CREATE TRIGGER statement’s SQL STATEMENT will be run and affect the CANADA_STATES table.

In the DELETE statement, it will delete one record from the CANADA_STATES table 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 30 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the DELETE statement will delete one record from the CANADA_STATES table. But if the DELETE statement is executed and it deletes no record from the CANADA_STATES table then the WHERE condition gets a FALSE value.

In the AS clause, we have used the SELECT statement to retrieve all records from the DELETED table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the GREATER THAN operator to find a value greater than 28 from the DELETED pseudo table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the DELETED table.

So, by using the AFTER DELETE trigger, it will delete one record from the CANADA_STATES table and a new record will be added to the DELETED pseudo table.

We hope that you have understood how to use the SQL Server AFTER trigger used with the INSERT and DELETE statements on the INSERTED and DELETED tables by the query. For a better explanation, we have used an example and explained it in depth.

Read: Trigger For Delete SQL Server

Update Another Table Using Trigger SQL Server

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_INSERTED
ON USA_STATES
AFTER UPDATE
AS 
SELECT * FROM INSERTED
WHERE STATE_ID=30;

UPDATE USA_STATES
SET STATE_NAME='Alabama'
WHERE STATE_ID=30;

As we see in the above query, a trigger is created AFTER_INSERTED on the USA_STATES table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger on the UPDATE statement which means that once the AFTER trigger is fired then the DML STATEMENT will be triggered. Then the SQL STATEMENT which is used inside the AS clause will be executed and make effected on the USA_STATES table.

In the UPDATE statement, it will update and set a new string_value of the STATE_NAME column as Alabama 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 30 from the USA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update a new value for that column in the USA_STATES table. But if the UPDATE statement is not able to update and the query will be executed because the WHERE condition gets a FALSE value.

In the AS clause, the SELECT statement is used to retrieve all records from the INSERTED pseudo table 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 30 from the INSERTED table.

If the WHERE condition turns out to be FALSE then the SELECT statement will be executed and will not retrieve any record for the result set from the INSERTED pseudo table. But if the SELECT statement retrieves all records from the INSERTED pseudo table then the WHERE condition turns out to be TRUE.

We hope that you have understood how to use the SQL Server AFTER trigger with the UPDATE statement on another table by the query. For a better description, we have used an illustration and clarified it in depth.

Read: SQL Server Trigger After Insert Update

SQL Trigger to Update Another Table

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

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER AFTER_CANADA
ON CANADA_STATES
AFTER UPDATE
AS 
SELECT * FROM INSERTED
ORDER BY STATE_ID DESC;

UPDATE CANADA_STATES
SET FULL_NAME='Tom Hardy'
WHERE STATE_ID=31;

In the aforementioned query, we have created a trigger called AFTER_CANADA on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we have to use the AFTER trigger on the UPDATE statement which means that once the trigger is fired when an event will occur. The SQL STATEMENT which is used inside the CREATE TRIGGER statement will occur and take an effect on the CANADA_STATES table.

In the UPDATE statement, it will update and set a new string_value of the FULL_NAME column as Tom Hardy 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 31 from the CANADA_STATES table.

If the WHERE condition turns out to be TRUE then the UPDATE statement will update and set a new string_value as in the CANADA_STATES table. But if the WHERE condition gets a FALSE value then the UPDATE statement will be executed and will update no new value for that column in the CANADA_STATES table.

In the AS clause, we have used the SELECT statement to retrieve 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 DESC keyword to arrange the records in descending order for the result set.

Once the order is arranged in the descending order then the SELECT statement will retrieve records from the INSERTED table.

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

You may also like to read the following SQL Server tutorials.

• Instead of Trigger In SQL Server
• SQL Server Date Format
• Recursive Trigger in SQL Server
• SQL Server First Day Of Month
• SQL Server Datetime functions
• SQL Server Trigger to check if value has changed
• SQL Server Trigger If Exists Raise Error

After reading this tutorial, we now know how to use the SQL Server Trigger on Delete Insert into Another Table. To assist you to understand the concept, we also covered a few examples. Here is a list of every subject we have discussed.

• SQL Server Trigger on Delete Insert Into Another Table
• SQL Trigger Delete Record Another Table
• SQL Server Trigger Deleted Row
• Inserted and Deleted Tables in SQL Server Trigger
• Update Another Table Using Trigger SQL Server
• SQL Trigger to Update Another Table
• Delete And Insert Into Another Table