Trigger For Delete in SQL Server

Here we will learn and comprehend how to use the Trigger For Delete SQL Server statement. To help you comprehend the idea better, we will also talk about and learn various examples. The complete list of subjects we will address is provided below.

• Trigger for Delete SQL Server
• Trigger For Insert Update Delete SQL Server
• Trigger Before Delete SQL Server
• Trigger After Delete SQL Server
• Trigger Inserted Deleted SQL Server
• Trigger Instead of Delete SQL Server
• Trigger Delete Record SQL Server
• Trigger Deleted Table SQL Server
• Trigger Inserted Deleted SQL Server

Trigger for Delete SQL Server

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

The DML trigger only activates after all actions described in the triggered SQL statements have successfully launched, according to the SQL Server FOR or AFTER trigger. Before a trigger may be successful, all referential cascade choices and constraint checks must be approved.

Here is an illustrated example of the SQL Server FOR trigger with the DELETE statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER FORDELETE
ON USA_STATES
FOR DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=15;

DELETE FROM USA_STATES
WHERE STATE_NAME= 'Hawaii';

As we see in the above query, we have created a trigger called FOR DELETE on the USA_STATES table by using the CREATE TRIGGER statement. The DML statement will be executed as soon as the trigger is activated, followed by the SQL STATEMENTS that were used inside the CREATE TRIGGER statement because the FOR trigger was then used on the DELETE statement.

In the DELETE statement, it will delete all records from the USA_STATES table used with the WHERE condition. In the WHERE condition, the STATE_NAME is used with the EQUAL TO operator to find a value equal to Hawaii from the USA_STATES table. If the WHERE condition turns out to be TRUE then the DELETE statement will delete all records from the USA_STATES table.

But if the WHERE condition turns out to be a FALSE value then the DELETE statement will delete no record from the USA_STATES table.

Once the DML statement is activated only when the SQL Server FOR trigger is triggered. The SELECT statement in the AS clause says:

• The SELECT statement will retrieve all records from the USA_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 15 from the USA_STATES table.
  • If the WHERE condition gets a TRUE value then the SELECT statement will retrieve all records from the USA_STATES table.
  • But if the WHERE condition turns out to be a FALSE value then the SELECT statement will retrieve an empty record set for the output which is used inside the USA_STATES table.

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

Read: SQL Server Trigger Update

Trigger For Insert Update Delete SQL Server

We will learn and understand how to use the SQL Server FOR trigger used with the INSERT, DELETE and UPDATE statements on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER FORDELETE
ON USA_STATES
FOR DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>=15;

INSERT INTO USA_STATES 
VALUES(25,'Mimai',52416,'Bill Gates','Male');

DELETE FROM USA_STATES
WHERE STATE_NAME= 'California';

UPDATE USA_STATES
SET FULL_NAME='Larry Page'
WHERE STATE_ID=20;

Using the CREATE TRIGGER statement in the previous query, we will create a trigger called FOR DELETE for the USA_STATES table. The DML statement i.e; (INSERT, DELETE, UPDATE ) will be performed first after the FOR trigger on the DML statement fires, followed by the SQL STATEMENT used inside the AS clause.

The INSERT statement is used to insert one new record into the USA_STATES table.

In the DELETE statement, it is used to delete one record from the USA_STATES table which is used with the WHERE condition. In the WHERE condition, the STATE_NAME column is used with the EQUAL TO operator to find a value equal to California from the USA_STATES table.

The DELETE statement will delete one record from the USA_STATES table if the WHERE condition turns out to be a TRUE value. If the WHERE condition gets a FALSE value then the DELETE statement will delete no record and the query will be executed with no deletion.

In the UPDATE statement, it’s used to update and set a new string_value of the FULL_NAME column as Larry Page from the USA_STATES table. In the WHERE condition, the STATE_ID column is used with the EQUAL TO operator to find a value equal to 20 from the USA_STATES table.

The UPDATE statement will update and set a value for that column only when the WHERE condition turns out to be TRUE. If the WHERE condition turns out to be FALSE then the UPDATE statement will be executed but no new string or numeric value for that column will be updated.

Once all the DML statement has been executed by the FOR trigger then the SELECT statement will be executed by saying that:

• When the WHERE condition is applied, the SELECT query will obtain every record from the USA_STATES table. Finding a number larger than or equal to 15 from the USA_STATES table requires using the GREATER THAN or EQUAL TO operator with the STATE_ID column in the WHERE condition.
  • The SELECT query will obtain all data from the USA_STATES table if the WHERE condition is TRUE.
  • However, the SELECT statement will obtain the empty record set for the output from the USA_STATES table if the WHERE condition turns out to be FALSE.

We hope that you have understood the subtopic “Trigger For Insert Update Delete SQL Server” by using the FOR trigger on the table by the query. We provided a detailed explanation and an example to serve as a better illustration.

Also, check: SQL Server Drop Trigger If Exists

Trigger Before Delete SQL Server

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

In SQL Server, there is no BEFORE trigger whereas the INSTEAD OF trigger is used on the DML STATEMENTS i,e; (INSERT, UPDATE and DELETE). The SQL Server INSTEAD OF trigger is used to skip the DML statements once the trigger is fired. Instead, it will run the SQL STATEMENTS which are used inside the AS clause.

Here we will see an illustrated example of the SQL Server INSTEAD OF DELETE trigger on the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_DELETE
ON USA_STATES
INSTEAD OF DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>15;

DELETE FROM USA_STATES
WHERE STATE_NAME= 'Jersey';

In the last query, we used the CREATE TRIGGER statement to create a trigger called INSTEAD OF DELETE on the USA_STATES table. The DELETE statement will then be skipped in favour of the SQL STATEMENT that is used inside the AS clause because the INSTEAD OF trigger was applied to the DELETE statement.

In the CREATE TRIGGER statement, the SELECT statement says that it will retrieve all records from the USA_STATES table which is 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 15 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. The SELECT statement will retrieve an empty record set from the USA_STATES table if the WHERE condition turns out to be a FALSE value.

The DELETE statement execution will be done once the INSTEAD OF trigger is fired but there will be no effect on the USA_STATES table. Here the DELETE statement execution says that:

• In the DELETE statement, it will delete one record from the USA_STATES table used with the WHERE condition. In the WHERE condition. the STATE_NAME column is used with the EQUAL TO operator to find a value equal to Jersey 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 WHERE condition turns out to be FALSE then the SELECT statement will retrieve an empty record set from the USA_STATES table.

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

Read: SQL Server Date Format

Trigger After Delete SQL Server

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

Similar to the SQL Server FOR trigger, the SQL Server AFTER trigger mechanism also functions. And they are identical to one another. And once the trigger is fired, the SQL Server FOR trigger fires once the SQL Server completes the activity’s execution.

Here is an illustrated example of the SQL Server AFTER trigger on the DELETE statement by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;
 
CREATE TRIGGER AFTER_DELETE
ON USA_STATES
AFTER DELETE
AS 
SELECT * FROM USA_STATES
WHERE STATE_ID>20;

DELETE FROM USA_STATES
WHERE STATE_NAME= 'Georgia';

In the above query, we have created a trigger called AFTER_DELETE by using the CREATE TRIGGER statement on the USA_STATES table. The DELETE statement will be executed after the AFTER trigger has fired, and after that, the SQL STATEMENT that was used in the CREATE TRIGGER statement will be run. This is because the AFTER trigger was used on the DELETE statement.

In the DELETE statement, it is used to delete one record from the USA_STATES table which is used on the WHERE condition. In the WHERE condition, the STATE_NAME column is used with the EQUAL TO operator to find a value equal to GEORGIA in the USA_STATES table.

If the WHERE condition turns out to be TRUE then the DELETE statement will delete the record from the USA_STATES table. The DELETE statement will be executed and no record will be deleted from the USA_STATES table if the WHERE condition turns out to be FALSE.

In the AS clause, the SELECT statement execution refers that:

• The SELECT statement will retrieve all records from the USA_STATES table which is 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 20 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 for the output if the WHERE condition gets a FALSE value.

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

Read: Introduction to SQL Server Trigger

Trigger Inserted Deleted SQL Server

We will learn and understand how to use the SQL Server AFTER trigger on the INSERTED and DELETED pseudos table by the query. And which will be explained with the help of an example.

In SQL Server, the DML trigger statement uses two types of special tables: the deleted table and inserted table. It automatically creates and handles those tables. We can use these temporary, memory-resident tables to test the effect of certain data modifications and set conditions for DML triggers.

Here are the affected of the table differently:

• Every time a record is added to the USA_STATES table, a new entry of that record is likewise added to the INSERTED table.
• Any time a record is removed from the USA_STATES table, an entry for that record is added to the DELETED table.
• Any time a record in the USA_STATES table is modified, an earlier version of that record is added to the DELETED table and a later version of that record is inserted into the INSERTED table.

Now we will test these cases. Let’s create a table called USA_STATES and a DML trigger for it. The trigger will be the same for the INSERT/ DELETE/ UPDATE operations. Here is an example of the SQL Server AFTER trigger on the INSERTED and DELETED pseudo table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER ORDERS_TRIGGERS
ON USA_STATES
AFTER INSERT, UPDATE, DELETE
AS 
BEGIN 
SELECT INSERTED.STATE_ID AS INSERTED_ID,
INSERTED.STATE_NAME AS INSERTED_NAME, 
INSERTED.STATE_ZIPCODE AS INSERTED_ZIPCODE,
INSERTED.FULL_NAME AS INSERTED_FULL_NAME,
INSERTED.GENDER AS INSERTED_GENDER
FROM INSERTED
SELECT DELETED.STATE_ID AS DELETED_ID,
DELETED.STATE_NAME AS DELETED_NAME, 
DELETED.STATE_ZIPCODE AS DELETED_ZIPCODE,
DELETED.FULL_NAME AS DELETED_FULL_NAME,
DELETED.GENDER AS DELETED_GENDER
FROM DELETED
END;

INSERT INTO USA_STATES VALUES
(26,'Ohio',56214,'Van Heising','Male');

DELETE FROM USA_STATES
WHERE STATE_ID=25;

UPDATE USA_STATES 
SET FULL_NAME='Chris Evans'
WHERE STATE_ID=26;

As we can see from the aforementioned query, we used the CREATE TRIGGER statement to create an ORDERS_TRIGGERS trigger on the USA_STATES table. Then, we executed the DML STATEMENTS (INSERT, DELETE, UPDATE) using the AFTER trigger. This indicates that it will execute the SQL STATEMENTS that are utilized inside the BEGIN and END statements after triggering the DML statement and doing so.

In the INSERT INTO statement, we have inserted one new record into the USA_STATES table.

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

If the WHERE condition turns out to be TRUE then the DELETE statement will delete one record from the USA_STATES table. But if the WHERE condition gets a FALSE value then the DELETE statement will be executed but no record will delete from the USA_STATES table.

In the UPDATE statement, it will update and set a new string_value for the FULL_NAME column as Chris Evans from the USA_STATES table and 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 26 from the USA_STATES table.

The UPDATE statement will update and set a new value for the USA_STATES only when the WHERE condition turns out to be TRUE. But if the value is not available in the record of the USA_STATES table then the UPDATE statement will be executed but that record will not be updated.

In the BEGIN statement, the SELECT statement is used to retrieve all records from the INSERTED and DELETED pseudo table. And we have used the ALIAS clause with the AS keyword and changed the USA_STATES column name into the INSERTED column_name for the INSERTED table.

This means that when we insert a new record into the USA_STATES table then that new same record will be inserted into the INSERTED table by using the INSERT INTO statement.

In the DELETED pseudo table, we have same used the ALIAS clause with the AS keyword and changed the USA_STATES column name into the DELETED column_name for the DELETED table. This means that by using the DELETE statement, we delete one particular record from the USA_STATES table and then add the same record into the DELETED pseudo table.

We hope that you have understood the subtopic “Trigger Inserted Deleted SQL Server” by using the SQL Server AFTER trigger on the DML statements of the table by the query. We have used an example and explained it in-depth, for better understanding.

Read: Drop stored procedure SQL Server

Trigger Instead of Delete SQL Server

Here we will learn and understand how to use the SQL Server INSTEAD OF trigger on the DELETE statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_DELETEHARVARD
ON HARVARD_UNIVERSITY
INSTEAD OF DELETE
AS 
SELECT STUDENT_ID,STUDENT_FIRSTNAME,
STUDENT_LASTNAME FROM HARVARD_UNIVERSITY
ORDER BY STUDENT_ID DESC;

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=10;

As we can see from the aforementioned query, we used the CREATE TRIGGER statement on the HARVARD_UNIVERSITY table to create a trigger with the name INSTEADOF_DELETEHARVARD.

Additionally, we used the INSTEAD OF trigger on the DELETE statement, which implies that it will execute the SQL STATEMENTS used in the CREATE TRIGGER statement rather than the DELETE statement.

The SELECT statement execution in the AS clause says that:

• In the SELECT statement, it will retrieve all records from the STUDENT_ID, STUDENT_FIRSTNAME and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table used with the ORDER BY clause.
  • And it will arrange the STUDENT_ID column in descending order by using the ORDER BY expression DESC keyword.

In the third above query, the DELETE statement execution will be executed but there will be no effect on the HARVARD_UNIVERSITY table because the INSTEAD OF trigger is fired. And trigger will always skip the DML statement once the trigger is fired.

The DELETE statement query says that it will delete one record from the HARVARD_UNIVERSITY table which is 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 10 from the HARVARD_UNIVERSITY table.

If the WHERE condition gets TRUE then the DELETE statement will be able to delete one record from the HARVARD_UNIVERSITY table. But if the WHERE condition turns out to be FALSE then the DELETE statement will delete no record and the remaining record will show the same on the HARVARD_UNIVERSITY table.

We hope that you have understood the subtopic “Trigger Instead of Delete SQL Server” by using the INSTEAD OF trigger on the table by the query. For a better answer, we have used an illustration example and presented it in depth.

Read: Alter view in SQL Server

Trigger For Update and Delete in SQL Server

Here we will learn and understand how to use the SQL Server FOR trigger on the UPDATE and DELETE statement on the table by the following query:

The SQL Server UNIQUE function is used to extract the unique values from column_name or the expression of the table by the query. It is also used the SELECT, DELETE, INSERT and UPDATE statements.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER INSTEADOF_HARVARD
ON HARVARD_UNIVERSITY
FOR DELETE, UPDATE
AS 
SELECT DISTINCT STUDENT_FIRSTNAME, STUDENT_ID
STUDENT_LASTNAME FROM HARVARD_UNIVERSITY;

DELETE FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID=12;

UPDATE HARVARD_UNIVERSITY 
SET STUDENT_FIRSTNAME='Damon'
WHERE STUDENT_ID=11;

Here in this query, a trigger is created called INSTEADOF_HARVARD on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. And the FOR trigger is fired during the DML statement is triggered.

In the DELETE statement, it is used to delete one record from the HARVARD_UNIVERSITY table which is used with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the EQUAL TO operator to find the value equal to 12 from the HARVARD_UNIVERSITY table.

In the UPDATE statement, it will update and set a new record for the STUDENT_FIRSTNAME column as Damon 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 11 from the HARVARD_UNIVERSITY table.

Once the execution of the DML statement is executed by the FOR TRIGGER, the SELECT statement will be executed as the query says:

• The SELECT statement is used to retrieve all records of the STUDENT_ID and STUDENT_LASTNAME columns from the HARVARD_UNIVERSITY table.
• The DISTINCT function is used on the STUDENT_FIRSTNAME column to extract unique values from the HARVARD_UNIVERSITY table.

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

Read: Indexed views in SQL Server

Trigger Delete Record SQL Server

In this SQL Server section, we will learn and understand how to delete records by using the FOR TRIGGER on the table by the query. And which will be explained with the help of an illustrated example.

In SQL Server, we need to work with the UPDATE and DELETE statement then we need to be aware of the affected rows in those events. The DELETED pseudo table has a copy of rows that will be either updated or deleted.

EXAMPLE:

USE SQLSERVERGUIDES; 

CREATE TRIGGER FOR_DELETE
ON USA_STATES
FOR INSERT
AS 
DELETE FROM USA_STATES
WHERE STATE_ID IN (SELECT deleted.STATE_ID FROM DELETED );

INSERT INTO USA_STATES 
VALUES(26,'Ohio',25534,'James Warne','Male');

In this initial query, a trigger is created called FOR_DELETE on the USA_STATES table by using the CREATE TRIGGER statement. Then the FOR trigger is used on the INSERT statement which means that the INSERT statement is triggered as once the FOR trigger is fired.

In the INSERT statement, it is used to insert one new record into the USA_STATES table. So, one record will be implemented inside the USA_STATES table. Then the DELETE statement execution in the AS clause says that it will delete one record from the USA_STATES table which is used with the WHERE condition.

In the WHERE condition, the STATE_ID column is used with the IN condition. In the IN condition, the SELECT statement will select records of the STATE_ID column from the DELETED pseudo table. This method means that a new record is inserted into the USA_STATES table while deletion is done from the dummy table a.k.a DELETED pseudo table.

We hope that you have understood the subtopic “Trigger Delete Record from SQL Server” by using the FOR trigger on the table by the query. For a better exposition, we have used an example and explained it in profundity.

Read: SQL Server Update Trigger Only If Column is Modified

Trigger Deleted Table SQL Server

Here we will learn and understand how to use the SQL Server FOR trigger on the DELETED pseudo table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES; 
 
CREATE TRIGGER FORDELETE_USASTATES
ON USA_STATES
FOR DELETE
AS 
SELECT DELETED.STATE_ID,DELETED.STATE_NAME
FROM DELETED;

DELETE FROM  USA_STATES
WHERE STATE_ID=26;

As we see in the overhead query, we have created a trigger called FORDELETE_USASTATES for the USA_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 after that the SQL STATEMENTS inside the CREATE TRIGGER statement will execute.

In the DELETE statement, it will delete the record from the USA_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 26 from the USA_STATES table.

If the WHERE condition is TRUE, the DELETE statement will remove the record from the USA_STATES table. However, if the WHERE condition is false, the DELETE statement will not remove any records from the USA_STATES table and will instead simply perform the query without any errors.

The SELECT statement will extract one record with the columns STATE_ID and STATE_NAME from the DELETED pseudo table, which is a dummy table, in the AS clause. This indicates that the specified column record will appear in the DELETED pseudo table along with the record that is deleted from the USA_STATES table by the DELETE statement.

We hope that you have understood the subtopic “Trigger Deleted Table SQL Server” by using the FOR trigger on the DELETED pseudo 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.

• Create a table from view in SQL Server
• SQL Server Substring Function
• Advanced Stored Procedure Examples
• Create a Database in SQL Server 2019

Consequently, in this post, we learned how to use the “Trigger For Delete SQL Server” statement. To assist you comprehend the idea better, we also discussed and learned about a variety of examples. The whole list of subjects we’ll discuss is provided below.

• Trigger for Delete SQL Server
• Trigger For Insert Update Delete SQL Server
• Trigger Before Delete SQL Server
• Trigger After Delete SQL Server
• Trigger Inserted Deleted SQL Server
• Trigger Instead of Delete SQL Server
• Trigger Delete Record SQL Server
• Trigger Deleted Table SQL Server
• Trigger Inserted Deleted SQL Server