Create Trigger in SQL Server for Insert and Update

In this SQL Server tutorial, we will learn and comprehend how to use Create Trigger in SQL Server for Insert and Update statements in this lesson. 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.

• Create Trigger in SQL Server for Insert and Update
• Create Trigger in SQL Server for Insert and Update Example
• Create Trigger SQL Server After Insert And Update
• Create Trigger in SQL Server For Insert Example
• Create Trigger In SQL Server For Insert And Update And Delete

Read: How to get inserted value in trigger SQL Server

Create Trigger in SQL Server for Insert and Update

In this SQL Server section, we will learn and understand how to use the SQL Server FOR trigger on the INSERT and UPDATE statement of the table by the following query:

On DML statements (such INSERT, UPDATE, and DELETE) and Stored Procedures that perform DML-like actions, triggers can be made in SQL Server. There are two sorts of DML Triggers. The After trigger (using the FOR/AFTER CLAUSE) fires following the successful completion by SQL Server of the activity that triggered it.

Here we will learn and understand how to use the CREATE TRIGGER statement with the FOR trigger on the INSERT and UPDATE statements are given below:

EXAMPLE:

USE SQLSERVERGUIDES;
GO

ALTER TRIGGER TR_FORTRIGGER
ON USA_STATES
FOR INSERT,UPDATE
AS 
BEGIN
SELECT * FROM USA_STATES
WHERE STATE_ID>=17;
END


BEGIN
INSERT INTO USA_STATES 
VALUES(28,'Mimai',52631,'Peter Parker','Male');
END

BEGIN
UPDATE USA_STATES
SET STATE_NAME='California'
WHERE STATE_ID=18;
END

In the aforementioned query, the USA_STATES table is given a trigger called TR_FORTRIGGER using the CREATE TRIGGER statement. Then we used the FOR trigger on the INSERT and UPDATE statements which means that it will execute these statements first then the SQL STATEMENTS will be executed which are used inside the CREATE TRIGGER statement.

In the INSERT statement, it is used to insert one new record into the USA_STATES table.

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

If the WHERE condition turns out to be TRUE then the UPDATE statement will update a new record for the USA_STATES table. But if the UPDATE statement gives an empty record set for the output from the USA_STATES table only when the WHERE gets a FALSE value.

In the AS clause, the SELECT statement is used to 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 18 from the USA_STATES table.

If the WHERE condition turns out to be a FALSE value then the SELECT statement will retrieve an empty record set 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.

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

Read: SQL Server First Day Of Month

Create Trigger in SQL Server for Insert and Update Example

Here we will learn and understand how to use the SQL Server FOR trigger used with the INSERT and UPDATE statement of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;
GO

CREATE TRIGGER TR_CANADASTATES
ON CANADA_STATES
FOR INSERT, UPDATE
AS
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID>=10;
END

BEGIN
INSERT INTO CANADA_STATES
VALUES(21,'Emma Watson','Ontario',52634);
END

BEGIN
UPDATE CANADA_STATES
SET CANADA_STATENAME='torento'
WHERE STATE_ID=10;
END

In the preceding query, we have used the CREATE TRIGGER statement to create a trigger called TR_CANADASTATES on the CANADA_STATES table. The FOR trigger is used on the INSERT and UPDATE statement to execute firstly as the trigger is fired then the SQL STATEMENTS which are used inside the CREATE TRIGGER statement are executed after.

The INSERT INTO statement has been used to insert a new record into the CANADA_STATES table.

The UPDATE statement has been used to update and set a string_value as TORONTO for 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 10 from the CANADA_STATES table.

Once the FOR trigger is triggered then the DML statement will be fired. After that, the SELECT statement will be executed and it says:

• The SELECT statement is used to retrieve all records from the CANADA_STATES table which is 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 10 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.
  • But the SELECT statement retrieves an empty record set for the output from the CANADA_STATES table only when the WHERE condition gives a FALSE value.

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

Read: SQL Server Datetime functions

Create Trigger SQL Server After Insert And Update

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

There is no difference between the SQL Server FOR trigger and SQL Server AFTER trigger. The method of both triggers is that once the trigger is fired then the DML STATEMENT will be triggered. The SQL Server BETWEEN condition is used to find a value between the two numbers for that column. And it is used inside the WHERE condition.

Here is an illustrated example of the SQL Server AFTER trigger used with the INSERT and UPDATE statements of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;
GO

CREATE TRIGGER AFTERINSERT_UPDATE
ON CANADA_STATES
AFTER INSERT, UPDATE
AS
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID BETWEEN 10 AND 25;
END

BEGIN
INSERT INTO CANADA_STATES
VALUES(22,'David Peterson','Quebec',23874);
END

BEGIN
UPDATE CANADA_STATES
SET FULL_NAME='India Hemsworth'
WHERE STATE_ID=16;
END

In this initial query, we have created a trigger called AFTERINSERT_UPDATE on the CANADA_STATES table by using the CREATE TRIGGER statement. The AFTER trigger is triggered on the DML statement (INSERT, UPDATE and DELETE) and then the DML statement will be triggered.

In the INSERT INTO statement, it is used to insert a new record into the CANADA_STATES table.

In the UPDATE statement, it is used to update and set a string_value of the FULL_NAME column as INDIA HEMSWORTH 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 16 from the CANADA_STATES table.

If the WHERE gets a TRUE value then the UPDATE statement will update a new value for that column from the CANADA_STATES table.

The AFTER trigger once fired and the DML statement has been triggered then the SQL STATEMENT query execution says:

• The SELECT statement retrieves all records from the CANADA_STATES table with the WHERE condition.
  • In the WHERE condition, the BETWEEN condition is used in the STATE_ID column to find a value between 10 and 25 from the CANADA_STATES table.
    • If the BETWEEN condition turns out to be TRUE then the WHERE will be also true which means that the SELECT statement will be able to retrieve all records from the CANADA_STATES table.
  • If the WHERE condition turns out to be a FALSE value then the SELECT statement will retrieve an empty record set for the output and the query will be executed without any error.

We hope that you have understood how to use the SQL Server AFTER trigger on the INSERT and UPDATE statement of the table by the query. For a better description, we have used a sample and presented it in depth.

Read: Difference between table and view in SQL Server

Create Trigger in SQL Server For Insert Example

We will learn and understand how to use the SQL Server FOR trigger on the INSERT statement on the table by the following query:

The SQL Server IN condition is used to find values from the expression or column_name by the query for the result set. It works the same as the OR condition.

EXAMPLE:

USE SQLSERVERGUIDES;
GO

CREATE TRIGGER TR_FORINSERT
ON CANADA_STATES
FOR INSERT
AS
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID IN (5,10,23,4,5);
END

BEGIN
INSERT INTO CANADA_STATES
VALUES(23,'Chris Brown','Alberta',52314);
END

In the preceding query, we have used the CREATE TRIGGER statement to create a trigger called TR_FORINSERT on the CANADA_STATES table. Then we used the FOR INSERT trigger on the CANADA_STATES table which means that the DML statement is triggered when the FOR trigger is fired.

The FOR trigger is fired then the INSERT INTO statement will insert a new record into the CANADA_STATES table.

In the AS clause, the SELECT statement is used to retrieve 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 IN condition to find values that are inside the parameters from the CANADA_STATES table.

In the IN condition, if the value of the parameter is founded from the CANADA_STATES table then the WHERE condition will get TRUE. If the WHERE condition is TRUE then the SELECT statement will retrieve all records from the CANADA_STATES table. But if the IN condition is not able to find any values from the parameters then the WHERE condition will be also a FALSE value.

But if the WHERE condition gets a FALSE value then the SELECT statement will retrieve empty records set for the output from the CANADA_STATES table.

We hope that you have understood how to use the SQL Server FOR trigger on the INSERT statement on the table by the query. For better knowledge, we have used an illustration and exemplified it in depth.

Read: Alter view in SQL Server

Create Trigger In SQL Server For Insert And Update And Delete

In this SQL Server subtopic tutorial, we will learn and understand how to use the SQL Server FOR trigger on the INSERT, UPDATE and DELETE statements of the table by the following query:

EXAMPLE:

USE SQLSERVERGUIDES;
GO

CREATE TRIGGER TR_FORINSERTDELETEUPDATE
ON CANADA_STATES
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT * FROM CANADA_STATES
WHERE STATE_ID IN (5,10,23,4,24);
END

BEGIN
INSERT INTO CANADA_STATES
VALUES(24,'Damon Salvatore','Torento',65214);
END

BEGIN
UPDATE CANADA_STATES
SET FULL_NAME='Phillips Heather'
WHERE STATE_ID=4;
END

BEGIN
DELETE FROM CANADA_STATES
WHERE STATE_ID=5;
END 

In this preceding query, a trigger is called TR_FORINSERTDELETEUPDATE on the CANADA_STATES table by using the CREATE TRIGGER statement. The FOR trigger is fired then the DML STATEMENT will be triggered.

In the INSERT INTO statement, it is used to insert a new record into the CANADA_STATES table.

In the UPDATE statement, it is used to update and set a string_value of the FULL_NAME column as Phillips Heather 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 4 from the CANADA_STATES table.

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

If the WHERE condition turns out to be TRUE then the UPDATE statement will update and set a new value for that column from the CANADA_STATES table.

In the AS clause, we have used the SELECT statement to retrieve all records from the CANADA_STATES table with the WHERE condition. In the WHERE condition, the STATE_ID column is used with the IN condition to find a value that is inside the parameters in the CANADA_STATES table.

The SELECT statement will retrieve all records from the CANADA_STATES table if the IN clause inside the WHERE condition turns out to be a TRUE value. If the WHERE condition turns out to be a FALSE value the SELECT statement will retrieve an empty record set for the output from the CANADA_STATES table.

We hope that you have understood how to use the SQL Server FOR trigger on the INSERT, UPDATE and DELETE statements of the table by the query. For a better understanding, we have used an illustrated example and explained it in deepness.

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

• Indexed views in SQL Server
• SQL Server logical operators
• View table in SQL Server
• Trigger definition in SQL Server
• Disable Trigger in SQL Server
• Delete a view in SQL Server

As a result, we discovered how to use the “Create Trigger in SQL Server for Insert and Update” statements in this post. We also discussed and learned about a range of instances to help you better understand the concept. The whole list of topics we’ll cover is given below.

• Create Trigger in SQL Server for Insert and Update
• Create Trigger in SQL Server for Insert and Update Example
• Create Trigger SQL Server After Insert And Update
• Create Trigger in SQL Server For Insert Example
• Create Trigger In SQL Server For Insert And Update And Delete