HTML
CSS
Bootstrap
JavaScript
C#
SQL
Salesforce Admin
Exercise
Study Material
-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
SQL Trigger
A trigger is a set of SQL statements that reside in system memory with unique names. It is a specialized category of stored procedure that is called automatically when a database server event occurs. Each trigger is always associated with a table.
A trigger is called a special procedure because it cannot be called directly like a stored procedure. The key distinction between the trigger and procedure is that a trigger is called automatically when a data modification event occurs against a table. A stored procedure, on the other hand, must be invoked directly.
The following are the main characteristics that distinguish triggers from stored procedures:
– We cannot manually execute/invoke triggers.
– Triggers have no chance of receiving parameters.
– A transaction cannot be committed or rolled back inside a trigger.
Syntax:
CREATE TRIGGER schema.trigger_name
ON table_name
AFTER {INSERT, UPDATE, DELETE}
[NOT FOR REPLICATION]
AS
{SQL_Statements}
The parameter descriptions of this syntax are illustrated below:
schema: It is an optional parameter that defines which schema the new trigger belongs to.
trigger_name: It is a required parameter that defines the name for the new trigger.
table_name: It is a required parameter that defines the table name to which the trigger applies. Next to the table name, we need to write the AFTER clause where any events like INSERT, UPDATE, or DELETE could be listed.
NOT FOR REPLICATION: This option tells that SQL Server does not execute the trigger when data is modified as part of a replication process.
SQL_Statements: It contains one or more SQL statements that are used to perform actions in response to an event that occurs.
Example 1:
CREATE TRIGGER mytrigger
ON TEST
FOR update
AS
BEGIN
PRINT ‘Table has been Updated Successfully’;
END;
You will get the following output:
Table has been Updated Successfully
(2 rows affected)
Completion time: 2024-02-07T17:47:57.1155910+05:30
Example 2:
You will get the following output:
TABLE IS CREATED SUCCESSFULLY
Completion time: 2024-02-07T17:57:35.6569227+05:30
Course Video
NOTE: Practice below practice questions on MSSQL SERVER, it will not execute on site editor.
1. CREATE TRIGGER ON DATABASE FOR CREATED_TABLE AND PRINT ‘NEW TABLE CREATE ON DATABASE’.
OUTPUT:
2. Create Trigger On Database For CREATED_TABLE, ALTER_TABLE and PRINT ‘TABLE CREATE OR MODIFIED ON DATABASE’.
OUTPUT:
3. CREATE TRIGGER ON DATABASE FOR DROP_TABLE AND PRINT ‘TABLE IS DROPED FROM DATABASE’.
OUTPUT:
4. Create Trigger on Table for INSERT and PRINT “INSERTED SUCCESSFULLY”.
OUTPUT:
5. Create Trigger on Table for UPDATE and PRINT “TABLE IS UPDATE SUCCESSFULLY!!!!!!”.
OUTPUT:
6. CREATE TRIGGER ON TABLE FOR UPDATE AND INSERT INTO LOG_TABLE STATUS COLOUMN ‘TABLE IS UPDATED ‘.
OUTPUT:
LOG_TABLE:
7.CREATE TRIGGER ON TABLE FOR RENAME THEN INSERT INTO “LOG_TABLE” Table’s “STATUS” column as “TABLE IS RENAMED”.
OUTPUT:
LOG_TABLE:
YouTube Reference :
1) SQL Trigger in Hindi/Urdu
2) SQL Trigger in English
