![Page 1: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/1.jpg)
SQL Server Triggers
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
Meganadha Reddy K.Technical Trainer | NetCom Learning
www.NetComLearning.com
![Page 2: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/2.jpg)
Agenda
• SQL Server Objects
• SQL Server Constraints
• Triggers
• Types of Triggers
• Demos
• Q & A
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 3: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/3.jpg)
SQL Server Objects
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
Tables
Views Columns
Constraints
Functions
Stored Procedures
Triggers
Joins
SysTables
Logins
indexes
Transactions
Backups
![Page 4: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/4.jpg)
TABLES
• Rows and Columns
• A database will have at least one table.
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
EmpId EmpName EmpDept
1 Meg IT
2 Harry Sales
3 Llew Admin
![Page 5: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/5.jpg)
Constraints
• Primary Key : Unique way to differentiate each row of a table.
• Unique Key : Same as above but allows null value only once
• Not Null
• Default
• Check
![Page 6: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/6.jpg)
SysTables
In Software Projects we frequently use below:
Sys.Columns
Sys.Objects
SysComments
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 7: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/7.jpg)
Additional Objects
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
• Stored Procedures
• Functions
• Views
• Triggers
• Cursors
• Joins : To retrieve data from more than one table
![Page 8: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/8.jpg)
TRIGGERS
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
Triggers
Instead of TriggersAfter Triggers
Insert Update Delete Insert Update Delete
![Page 9: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/9.jpg)
Demo : Trigger (1 of 3)
• Lets take table : Employee
CREATE TABLE Employee
(
Emp_ID INT Identity,
Emp_name Varchar(100),
Emp_Sal Decimal (10,2)
)
INSERT INTO Employee VALUES (‘James',1200);
INSERT INTO Employee VALUES (‘Nick',1400);
INSERT INTO Employee VALUES (‘Raj',1000);
INSERT INTO Employee VALUES (‘Ravi',1100);
INSERT INTO Employee VALUES (‘Llew',1500);
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 10: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/10.jpg)
Demo : Trigger (2 of 3)
CREATE TABLE Employee_Audit
(
Emp_ID int,
Emp_name varchar(100),
Emp_Sal decimal (10,2),
Audit_Action varchar(100),
Audit_Timestamp datetime
)
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 11: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/11.jpg)
Demo : Trigger (3 of 3)
CREATE TRIGGER trgAfterInsert ON [dbo].[Employee] FOR INSERT
AS
declare @empid int;
declare @empname varchar(100);
declare @empsal decimal(10,2);
declare @audit_action varchar(100);
select @empid=i.Emp_ID from inserted i;
select @empname=i.Emp_Name from inserted i;
select @empsal=i.Emp_Sal from inserted i;
set @audit_action='Inserted Record -- After Insert Trigger.';
insert into Employee_Audit
(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)
values(@empid,@empname,@empsal,@audit_action,getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 12: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/12.jpg)
Triggers
More Demos
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/
![Page 13: SQL Server Triggers - netcomlearning.s3.amazonaws.com · Demo : Trigger (1 of 3) •Lets take table : Employee CREATE TABLE Employee (Emp_ID INT Identity, Emp_name Varchar(100), Emp_Sal](https://reader036.vdocuments.us/reader036/viewer/2022062601/5d3ba2f888c993332b8b994f/html5/thumbnails/13.jpg)
Q & A
?
© Meganadha Reddy K., 2015 http://www.netcomlearning.com/