Trigger is a special kind of stored procedure, which “reacts” to certain actions we make in the database. The main idea behind triggers is that they always perform an action in case some event happens.
Types of Trigger:
- DML (data manipulation language) triggers – We’ve already mentioned them, and they react to DML commands. These are – INSERT, UPDATE, and DELETE
- DDL (data definition language) triggers – As expected, triggers of this type shall react to DDL commands like – CREATE, ALTER, and DROP
- Logon triggers – The name says it all. This type reacts to LOGON events
Instead of Vs After Trigger
An INSTEAD OF trigger is a trigger that allows you to skip an INSERT, DELETE, or UPDATE statement to a table or a view and execute other statements defined in the trigger instead. The actual insert, delete, or update operation does not occur at all.
After Trigger fire after execution of INSERT, UPDATE, DELETE.
DML Trigger:
Syntax of DML Trigger:
CREATE TRIGGER [schema_name.]trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
{sql_statements}
Syntax to Drop Trigger:
DROP TRIGGER [schema_name.]trigger_name;
SQL Insert Trigger: It will perform check before the INSERT statement.
INSTEAD OF INSERT
create trigger ins_tri on employee INSTEAD OF INSERT
AS
BEGIN
declare @name varchar(100);
declare @dept varchar(100);
declare @salary int;
declare @city varchar(100);
declare @manager varchar(100);
declare @state varchar;
declare @dept_head varchar;
select @name=[name],@dept=dept,@salary=salary,@city=city,@manager=manager,@state=[state],@dept_head=dept_head
from inserted;
if @name is null set @name='Missing';
if @salary is null set @salary=10000;
insert into employee ([name],[dept],salary,city,manager,[state],dept_head)values(@name,@dept,@salary,@city,@manager,@state,@dept_head);
end
After Insert:
create trigger AFT_ins_tri on employee AFTER INSERT
AS
BEGIN
select * from inserted
end
--select * from employee
insert into employee values('amit','it',2000,'bengalurur','sonu','karnatka','amar')
Delete Trigger: It will perform check before delete statement, whether row should be deleted or not.
Instead of Delete
create trigger delete_tri on employee INSTEAD OF Delete
AS
BEGIN
declare @id int;
select @id=id from deleted;
if @id not in (1,2)
delete from employee where id=@id;
else
Throw 51000,'cant delete this id',1;
end;
delete from employee where id=1
After Delete
create trigger AFT_del_tri on employee AFTER Delete
AS
BEGIN
declare @id int
declare @datetime datetime
select @id=id from deleted
select @datetime=getdate()
insert into log_data values(@id,'deleted',@datetime)
end
delete from employee where id=33
select * from log_data
--select * from employee
--create table log_data(id int,event_d varchar(50),date_t datetime)
--drop table log_data
Update Trigger: It will perform check before Update statement, whether row should be Update or not.
After Update:
create trigger AFT_upd_tri on employee AFTER Update
AS
BEGIN
declare @id int
declare @datetime datetime
--declare @old_salry int
--declare @new_salary int
select @id=id from deleted
select @datetime=getdate()
insert into log_data values(@id,'updated',@datetime)
end
update
employee
set salary=25000
where id=32
select * from log_data
Instead of Update:
create trigger inst_upd_tri on employee INSTEAD OF update
AS
BEGIN
declare @salary int;
declare @id int;
select @salary=salary from inserted;
select @id=id from deleted
if @salary< 25000
update employee set salary=25000
where id=@id;
else
update employee set salary=@salary
where id=@id;
end;
update
employee
set salary=15000
where id=32
select * from log_data
DDL Trigger:
SQL Server DDL triggers respond to server or database events rather than to table data modifications. These events created by the Transact-SQL statement that normally starts with one of the following keywords
CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS.The DDL triggers are useful in the following cases:
- Record changes in the database schema.
- Prevent some specific changes to the database schema.
- Respond to a change in the database schema.
Syntax to Create DDL Trigger:
CREATE TRIGGER trigger_name
ON { DATABASE | ALL SERVER}
[WITH ddl_trigger_option]
FOR {event_type | event_group }
AS {sql_statement}
Use
DATABASE if the trigger respond to database-scoped events or ALL SERVER if the trigger responds to the server-scoped events.The
ddl_trigger_option specifies ENCRYPTION and/or EXECUTE AS clause. ENCRYPTION encrypts the definition of the trigger. EXECUTE AS defines the security context under which the trigger is executed.The
event_type indicates a DDL event that causes the trigger to fire e.g., CREATE_TABLE, ALTER_TABLE, etc. The event_group is a group of event_type event such as DDL_TABLE_EVENTS.Lets Create a Log Table:
CREATE TABLE logs (
log_id INT IDENTITY PRIMARY KEY,
event_data XML NOT NULL,
changed_by SYSNAME NOT NULL
)
Create Trigger:
CREATE TRIGGER trg_index_change ON DATABASE FOR CREATE_INDEX,ALTER_INDEX, DROP_INDEX
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO logs (event_data,changed_by)VALUES (EVENTDATA(),USER);
END;
GO
CREATE NONCLUSTERED INDEX nidx_lname
ON employee(dept);
Select * from logs
Disable and Enable Trigger:
Disable Trigger:
DISABLE TRIGGER [schema_name.][trigger_name]
ON [object_name | DATABASE | ALL SERVER]
Disable all Trigger on Table
DISABLE TRIGGER ALL ON table_name;
Disable all Trigger on Database:
DISABLE TRIGGER ALL ON DATABASE;
Enable all Trigger of Table:
ENABLE TRIGGER ALL ON table_name;
Enable all Trigger of Database:
ENABLE TRIGGER ALL ON DATABASE;
Get Trigger Definition:
EXEC sp_helptext 'sales.trg_members_delete' ;