.png)
TRIGGERS in SQL Server
A trigger is a Stored procedure that gets Activated on special events. There are two kinds of triggers (DDL) Data Definition Language triggers and (DML) Data Manipulation Language triggers.
Unlike Stored Procedures you cannot explicitly invoke the code of a trigger, triggers are activated automatically on special events like INSERT, UPDATE, or DELETE.
You cannot pass parameters to a Trigger as you do in a Store Proc, Triggers also don’t have any Return code.
There are TWO types of TRIGGERS
DDL Triggers
- CREATE
- ALTER
- DROP
DML Triggers
- INSERT
- UPDATE
- DELETE
Below is the syntax for creating a TRIGGER in SQL Server.
CREATE OR ALTER TRIGGER [Trigger Namel ON [Table or View Name] {FOR | AFTER | INSTEAD OF} INSERT AS /* Business Logic.... /* GO;
MAGIC TABLE :- INSERTING &DELETING
When the trigger is Activated SQL server creates two Virtual tables “INSERTING” & “DELETED”.
“INSERTING” will hold the value of records that are being Inserted and “DELETED” will hold the value of records that are being deleted.
FOR | AFTER TRIGGERS
TO UNDERSTAND THE CONCEPT OF “FOR UPDATE”, “FOR INSERT” & “FOR DELETE” We will create a table and apply these actions one by one so that we can understand better.
IF OBJECT_ID(N'EMPLOYEES', N'U') IS NOT NULL DROP TABLE EMPLOYEES; CREATE TABLE EMPLOYEES (ID INT,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT); INSERT INTO EMPLOYEES VALUES (1,'SHANE','RAY',5000),(2,'JACK','STRONG',7000),(3,'OLGER','PHILLIPS',9000); select * from EMPLOYEES;
FOR DELETE | AFTER DELETE
When you create a FOR DELETE Trigger on a table the trigger will get activated when there is a row deleted from a table and a copy of deleted rows will be created in the Virtual / Magic table DELETED, these copies of rows can then be used to maintain a history table.
Please note that the Scope of Magic table “DELETED” is only while the Trigger is running, so you will not be able to access the Magic tables before the execution of the trigger and after the execution of the trigger, magic tables are only available while the trigger is executing
IF OBJECT_ID ('EMP_TRIG_UPDATE', 'TR') IS NOT NULL
DROP TRIGGER EMP_TRIG_UPDATE;
GO
CREATE TRIGGER EMP_TRIG_UPDATE
ON EMPLOYEES
FOR DELETE
AS
SELECT 'INSERTED' ACTION,ID,F_NAME,L_NAME,SALARY FROM INSERTED;
SELECT 'DELETED' ACTION,ID,F_NAME,L_NAME,SALARY FROM DELETED;
GO
DELETE FROM EMPLOYEES WHERE ID=3;
FOR INSERT | AFTER INSERT
When you create a FOR INSERT Trigger on a table the trigger will get activated when there is a row inserted into a table and a copy of inserted rows will be created in the Virtual / Magic table INSERTED, These copies of rows can then be used to maintain a history table.
IF OBJECT_ID ('EMP_TRIG_UPDATE', 'TR') IS NOT NULL
DROP TRIGGER EMP_TRIG_UPDATE;
GO
CREATE TRIGGER EMP_TRIG_UPDATE
ON EMPLOYEES
FOR INSERT
AS
SELECT 'INSERTED' ACTION,ID,F_NAME,L_NAME,SALARY FROM INSERTED;
SELECT 'DELETED' ACTION,ID,F_NAME,L_NAME,SALARY FROM DELETED;
GO
INSERT INTO EMPLOYEES VALUES
(5,'JACK','RAY',5000);
FOR UPDATE | AFTER UPDATE
When you create a FOR UPDATE Trigger on a table the trigger will get activated when there is a row UPDATED into the table and a copy of the inserted row will be created in the Virtual / Magic table INSERTED AND DELTED, Please note that when we update a row the same row is deleted and a new row is inserted, so we will have the data in both INSERTED and DELTED magic table.
IF OBJECT_ID ('EMP_TRIG_UPDATE', 'TR') IS NOT NULL
DROP TRIGGER EMP_TRIG_UPDATE;
GO
CREATE TRIGGER EMP_TRIG_UPDATE
ON EMPLOYEES
FOR UPDATE
AS
SELECT 'INSERTED' ACTION,ID,F_NAME,L_NAME,SALARY FROM INSERTED;
SELECT 'DELETED' ACTION,ID,F_NAME,L_NAME,SALARY FROM DELETED;
GO
UPDATE EMPLOYEES SET ID=4 WHERE ID=3;
Please note that “UPDATE”,” INSERT” & “DELETE” can be used in one statement as shown below.
Triggers for Data Integrity
Triggers can be used in place of CHECK constraints. In some cases, Triggers can Replace “CHECK” Constraints and can be the preferable choice.
You should use Triggers when
- You need to check the data from different tables before an UPDATE, INSERT or DELETE. Note that CHECK Constraint can reference other columns but triggers can reference other tables.
- Where you need to check the DELTA (Before or After ) of an update.
- when you want to show a User Defined error.
In the below example, we are creating EMPLOYEES and DEPARTMENTS tables, the goal here is if there is no valid Department name in the department’s table then SQL should not allow an entry in the Employees table
IF OBJECT_ID(N'EMPLOYEES', N'U') IS NOT NULL DROP TABLE EMPLOYEES; IF OBJECT_ID(N'departments', N'U') IS NOT NULL DROP TABLE departments; CREATE TABLE EMPLOYEES (ID INT,F_NAME VARCHAR(20),L_NAME VARCHAR(20),SALARY INT,dept varchar(20)); INSERT INTO EMPLOYEES VALUES (1,'SHANE','RAY',5000,'IT'),(2,'JACK','STRONG',7000,'IT'),(3,'OLGER','PHILLIPS',9000,'HR'); CREATE TABLE departments (ID INT,dept_name varchar(20)); INSERT INTO departments VALUES (1,'IT'),(2,'HR'); select * from EMPLOYEES; select * from departments;
IF OBJECT_ID ('EMP_TRIG_UPDATE', 'TR') IS NOT NULL
DROP TRIGGER EMP_TRIG_UPDATE;
GO
CREATE TRIGGER EMP_TRIG_UPDATE
ON EMPLOYEES
FOR UPDATE ,INSERT ,DELETE
AS
IF EXISTS
(
SELECT *
FROM EMPLOYEES a
full outer JOIN DEPARTMENTS d
ON a.dept = d.dept_name
WHERE d.dept_name IS NULL
)
BEGIN
RAISERROR('No Such Departments Exists. Transaction Failed.',16,1);
ROLLBACK TRAN;
END;
go
--insert into EMPLOYEES values (2,'JACK','STRONG',7000,'admin')
Important Notes
- Use the “WITH ENCRYPTION” option to hide the code of a Trigger, this works the same way as it works in the case of a view.
- Triggers can be nesting to 32 levels.
0 Comments