SQL Server Update Trigger Only If Column is Modified

This SQL Server tutorial will discuss the implementation of SQL Server Update Trigger Only If Column is Modified. We will discuss and draw lessons from several cases to help you comprehend the subject better. The whole list of subjects we’ll discuss is provided below.

• SQL Server Update Trigger Only If Column is Modified
• SQL Server Update Trigger Only When Column is Modified
• SQL Server Modified Date
• SQL Server Update Modified Date Trigger

SQL Server Update Trigger Only If Column is Modified

Here we will learn and understand how to use the SQL Server AFTER trigger used with the INSERT and UPDATE statement to modify one column in the table by the query. And which is explained with the help of an illustrated example.

EXAMPLE:

USE sqlserverguides;

CREATE TRIGGER UPDATECOLUMN
ON CANADA_STATES
AFTER INSERT, UPDATE
AS
IF ( UPDATE (CANADA_STATENAME) )
BEGIN
UPDATE CANADA_STATES
SET FULL_NAME='Anthony James'
WHERE STATE_ID IN (SELECT DISTINCT STATE_ID FROM inserted)
END;

INSERT INTO CANADA_STATES
VALUES(37,'Robert Peterson','Yukente',54522);

SELECT * FROM CANADA_STATES
wHERE STATE_ID>=35;

In this aforementioned query, a trigger is created called UPDATECOLUMN on the CANADA_STATES table by using the CREATE TRIGGER statement. Then we used the AFTER trigger with the INSERT and DELETE statement which means that once the AFTER 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 from the CANADA_STATES table after the AFTER trigger is fired.

in the AS clause, we have used the IF statement with the UPDATE function. The UPDATE function is used to update the CANADA_STATENAME column of the CANADA_STATES table. In the BEGIN statement, the UPDATE statement is used to update and set a string_name of the FULL_NAME column as Anthony James which is based on the WHERE condition.

The STATE_ID column is used with the IN condition in the WHERE condition. In the IN condition, the SELECT statement is used with the DISTINCT function to find the unique id of the STATE_ID column from the INSERTED table.

As we see that the UPDATE statement updated a new value of the FULL_NAME column as Anthony James but when we used the INSERT INTO statement to insert a new record then it updated the new value to the old value of the FULL_NAME column, So, to check it we have used the SELECT statement and here its execution says:

• In the SELECT statement, it will retrieve all records from the CANADA_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 35 from the CANADA_STATES table.
    • If the WHERE condition turns out to be TRUE then the SELECT statement retrieves all records from the CANADA_STATES table.
    • But if the SELECT statement retrieves an empty record set from the CANADA_STATES table and it is executed only when the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server Update Trigger Only If Column is Modified” by using the AFTER trigger with the INSERT and 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 Trigger on Delete Insert Into Another Table

SQL Server Update Trigger Only When Column is Modified

Here we will learn and understand the subtopic “SQL Server Update Trigger Only When Column is Modified” by using the SQL Server AFTER trigger on the table by the query and which is explained with the help of an illustrated example.

EXAMPLE:

USE sqlserverguides;

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

INSERT INTO USA_STATES
VALUES(31,'Denver',54522,'Tom Shelby','Male');

SELECT * FROM USA_STATES
wHERE STATE_ID>10 
ORDER BY STATE_ID ASC;

As we see in the above query, we have used the CREATE TRIGGER statement to create a trigger called UPDATECOLUMN_IFMODIFIED on the USA_STATES table. Then we have used the AFTER trigger used with the INSERT and UPDATE statement which means that once the trigger is fired the DML STATEMENT will be triggered and make an effect on the USA_STATES table.

In the AS clause, the IF statement is used with the UPDATE statement which means that it will update the STATE_NAME column of the USA_STATES table. In the BEGIN statement, the UPDATE statement will update and set a string_value of the FULL_NAME column as Robert Peterson in the USA_STATES table.

The STATE_ID column is used with the IN condition in the WHERE condition. In the IN condition, the SELECT statement will retrieve the unique id of the STATE_ID column with the help of a DISTINCT clause in the INSERTED table. To close it, we have used the END statement.

In the second query, we have inserted a new record into the USA_STATES table by using the INSERT INTO statement. A new record will be inserted into the USA_STATES table but it will also be available in the INSERTED table. But there will be a small difference because it will update the old value to the new value of the new record in the USA_STATES table by using the UPDATE statement in the CREATE TRIGGER statement.

To check the new record with updated value in the USA_STATES table by using the SELECT statement. Here the execution of the SELECT statement says that:

• The SELECT statement retrieves 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 operator to find a value greater than 20 from the USA_STATES table.
• And we have also used the ORDER BY clause to arrange the records of the STATE_ID column in ascending order.
• If the WHERE condition is used to TRUE then the SELECT statement will retrieve records from the USA_STATES table.
• But if the SELECT statement retrieves empty records for the output and the query gets executed then the WHERE condition gets a FALSE value.

We hope that you have understood how to use the SQL Server AFTER trigger with the UPDATE statement to update a column if modified in the table by the query. For better knowledge, we have used an illustration and explained it in depth.

Read: SQL Server Create Trigger If Not Exists

SQL Server Modified Date

We will learn and understand how to create a modified date of the column of the table by a trigger in the query and which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER TR_MODIFIED_COLUMN
ON HARVARD_UNIVERSITY
AFTER UPDATE, INSERT
AS 
UPDATE HARVARD_UNIVERSITY
SET STUDENT_ADMITDATE=CURRENT_TIMESTAMP
WHERE STUDENT_ID IN (SELECT DISTINCT STUDENT_ID FROM INSERTED);

INSERT INTO HARVARD_UNIVERSITY
VALUES(17,'David','Keth','Keth.david@gmail.com','Male','2022-03-07','2026-05-31');

SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID>=11;

In this aforementioned query, a trigger is created called TR_MODIFIED_COLUMN on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger on the INSERT and UPDATE statement which means that once the AFTER trigger is fired and the DML STATEMENT will be executed and triggered, Then the SQL STATEMENT which is used inside the CREATE TRIGGER statement.

Then we inserted a new record into the HARVARD_UNIVERSITY table by using the INSERT INTO statement. A new record has been inserted because the trigger is fired.

In the AS clause, the UPDATE statement is used to update and set a new string_value of the STUDENT_ADMITDATE column as the current date value by using the CURRENT_TIMESTAMP function. The STUDENT_ID column is used with the IN condition in the WHERE condition.

In the IN condition, the SELECT statement is used with the DISTINCT clause to find the unique id of the STUDENT_ID column from the INSERTED table. If the STUDENT_ID column gets worked with the IN condition only when the WHERE condition gets TRUE value otherwise vice-versa.

To check the updated value of the DATE column gets modified, we have used the SELECT statement to retrieve all records from the HARVARD_UNIVERSITY table. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 11 from the HARVARD_UNIVERSITY table.

If the WHERE condition turns out to be TRUE then the SELECT statement will retrieve all records from the HARVARD_UNIVERSITY table. But if the SELECT statement retrieves an empty record set from the HARVARD_UNIVERISTY table and it is executed only when the WHERE condition gets a FALSE value.

We hope you have understood the subtopic “SQL Server Modified Date” by using the SQL Server AFTER trigger used with the INSERT and UPDATE columns from the table by the query. For better knowledge, we have used an illustration and clarified it in depth.

Read: SQL Server Trigger Before Insert

SQL Server Update Modified Date Trigger

Here we will learn and understand how to use the SQL Server UPDATE trigger to modify the date of the table by the query and which will be explained with the help of an illustrated example.

EXAMPLE:

USE SQLSERVERGUIDES;

CREATE TRIGGER DATE_MODIFIED
ON HARVARD_UNIVERSITY
AFTER INSERT,UPDATE
AS
UPDATE HARVARD_UNIVERSITY
SET STUDENT_ADMITDATE= CURRENT_TIMESTAMP
WHERE STUDENT_ID IN (SELECT DISTINCT STUDENT_ID FROM INSERTED)

INSERT INTO HARVARD_UNIVERSITY
VALUES(18,'Emma','Watson','watson.emma@hollywood.com','Female','2021-06-04','2024-04-06');

SELECT * FROM HARVARD_UNIVERSITY
WHERE STUDENT_ID >=14
ORDER BY STUDENT_ID DESC;

In this aforementioned query, we have created a trigger called DATE_MODIFIED on the HARVARD_UNIVERSITY table by using the CREATE TRIGGER statement. Then we have used the AFTER trigger with the INSERT and UPDATE statement which means that once the trigger is fired then the DML STATEMENT is triggered and will take effect on the HARVARD_UNIVERSITY table. Then the SQL STATEMENT which is used inside CREATE TRIGGER statement will be executed.

Once the trigger is created then we have used the INSERT INTO statement to insert a new record into the HARVARD_UNIVERSITY table as the AFTER trigger is fired.

In the AS clause, the UPDATE statement is used to update and set a new date_value of the STUDENT_ADMITDATE column as the CURRENT_TIMESTAMP function. The STUDENT_ID column is used with the IN condition in the WHERE condition. In the IN condition, the SELECT statement is used to retrieve the unique id of the STUDENT_ID column by using the DISTINCT clause on the INSERTED table.

In the SELECT statement, it will retrieve all records from the HARVARD_UNIVERISTY table with the WHERE condition. In the WHERE condition, the STUDENT_ID column is used with the GREATER THAN or EQUAL TO operator to find a value greater than or equal to 14 and arrange the STUDENT_ID column in descending order by using the ORDER BY expression DESC in the HARVARD_UNIVERSITY table.

If the WHERE condition turns out to be TRUE then the SELECT statement retrieves all records from the HARVARD_UNIVERSITY table. But if the SELECT statement has been executed and retrieves an empty record set from the HARVARD_UNIVERSITY table only when the WHERE condition gets a FALSE value.

We hope that you have understood the subtopic “SQL Server Update Modified Date Trigger” by using the SQL Server AFTER trigger on the INSERT and UPDATE statements of the table by the query. For a better exposition, we have used a sample example and clarified it in depth.

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

• Create Trigger in SQL Server for Insert and Update
• How to get inserted value in trigger SQL Server
• RIGHT JOIN in SQL Server
• SQL Server OUTER JOIN + Examples
• How to get trigger definition in SQL Server
• How to execute Trigger in SQL Server
• SQL Server Trigger Update with Examples
• Stored procedure in SQL Server for insert and update

We now know How to get inserted value in trigger 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.

• SQL Server Update Trigger Only If Column is Modified
• SQL Server Update Trigger Only When Column is Modified
• SQL Server Modified Date
• SQL Server Update Modified Date Trigger