Module Overview
• Triggers
• QBE
Triggers
• In SQL server there are 3 types of triggers
– DML triggers
– DDL triggers
– Logon trigger
• DML triggers are fired automatically in response to DML events
(INSERT, UPDATE & DELETE)
• DML triggers can again classified into 2 types
– After triggers (sometimes called as FORtriggers)
– Instead of triggers
Triggers
• After triggers, fires after the triggering action.
– The INSERT, UPDATE, and DELETE statements, causes an after trigger
to fire after the respective statements complete execution.
• INSTEAD of triggers, fires instead of the triggering action.
– The INSERT, UPDATE, and DELETE statements, causes an INSTEAD OF
trigger to fire INSTEAD OF the respective statement execution (i.e.,
before executing respective statement).
After/For Trigger
Create trigger tr_employee_ForInsert
ON HR.employees
FOR INSERT
AS
BEGIN
Declare @Id int
select @Id = employee_id from inserted
insert into HR.employeeAudit values ('New employee with Id = ‘ + cast(@Id as nvarchar(5)) + ' is
added at ‘ + cast(GetDate() as nvarchar(20))
End
insert into HR.employees values ('Mike','Ben','test@yahoo.com','9845033443','2020-01-06',1,5000,205,11)
After DeleteTrigger
Create trigger tr_employee_ForDelete
ON HR.employees
FOR DELETE
AS
BEGIN
Declare @Id int
select @Id = d.employee_id from deleted d
insert into HR.employeeAudit values ('An existing employee with Id = ' + cast(@Id as nvarchar(5))
+ ' is deleted at ' + cast(Getdate() as nvarchar(20))
End
Delete from HR.employees where employee_id=1
After UpdateTrigger
Create trigger tr_employee_ForUpdate
ON HR.employees
FOR update
AS
BEGIN
select * from deleted
select * from inserted
End
update HR.employees set first_name ='Mike', salary=2000 where employee_id=206
• Note:
• The After trigger for UPDATE event, makes use of both inserted and delete tables.
• The inserted table contains the updated data and the deleted table contains the old data.
Instead of InsertTrigger
create trigger tr_vWEmployeeDetails_InsteadofInsert
on vWEmployeeDetails
Instead of Insert
as
Begin
Declare @DeptId int
--Check if there is a valid DepartmentID for the given department name
select @DeptId = d.department_id from HR.departments d join inserted i
on i.department_name = d.department_name
--If DepartmentId is null throw an error
--and stop processing
if(@DeptId is null)
Begin
RAISERROR('Invalid Department Name. Statement terminated',16,1)
return
End
--Finally insert into HR.employees table
Insert into HR.employees(first_name,last_name,email,hire_date,job_id,salary,department_id)
Instead of InsertTrigger
insert into vWEmployeeDetails values ('fname','lname','test@yahoo.com','1989-09-21',4,3000,'IT')
• Note:
• The After trigger for UPDATE event, makes use of both inserted and delete tables.
• The inserted table contains the updated data and the deleted table contains the old data.
Instead of update Trigger
create view vWEmployeeDetails2
as
select e.employee_id,first_name, e.last_name, e.email,e.hire_date,e.job_id,e.salary, d.department_name
from HR.Employees e
join HR.departments d
on e.department_id = d.department_id
• Update the view, in such a way that, it affects, both the underlying tables
update vWEmployeeDetails2 set first_name='Johny', department_name='IT' where
employee_id =211
Instead of update Trigger
End
-- department name is updated
if(update(department_name))
Begin
Declare @DeptId int
select @DeptId = d.department_id from HR.departments d join inserted i
on i.department_name = d.department_name
if(@DeptId is NULL)
Begin
RAISERROR('employee id can not be changed',16,1)
return
END
update HR.employees set department_id = @DeptId
from inserted i join HR.employees e on e.employee_id=i.employee_id
--if name is updated
if(update(first_name))
Begin
update HR.employees set first_name = i.first_name
from inserted i join HR.employees e on e.employee_id=i.employee_id
End
Instead of Delete Trigger
create trigger tr_vWEmployeeDetails_InsteadofDelete
on vWEmployeeDetails2
instead of delete
as
Begin
Delete HR.employees
from HR.employees join deleted
on HR.employees.employee_id = deleted.employee_id
END
Delete from vWEmployeeDetails2 where employee_id = 211
DDL Trigger
create trigger trMyFirstTrigger
on database
for create_table, Alter_table, Drop_table
as
Begin
Print 'New table created'
End
create table test (id int)
Query By Example (QBE)
QBE is a method of searching a database in where the DBMS software provides an interface
for running searches
Users only have to enter the criteria for their search and the DBMS software will run the query. This differs from SQL where
a specific code is required for running queries of a databases.
Example is MS Acess Database
First Developed by IBM
Query By Example (QBE)
Programming with Transact-SQL
• Triggers
• QBE
• Lab: Programming with Transact-SQL
©2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the
U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft
must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after
the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
