SQL SERVER TUTORIAL 3::: What’s New in Creating and Managing Triggers
CONTENTS
- INTRODUCTION
- TYPES OF TRIGGES
- AFTER TRIGGER
- AFTER TRIGGER IN DB2
- INSTEAD OF TRIGGER
- DISABLE TRIGGER
- TRIGGERS AND SECURITY
- EXAMPLE TO IMPLEMENT VIRTUAL TABLE FROM TRIGGER
- INTRODUCTION
Microsoft has expanded the capabilities of triggers in SQL Server 2005. The bulk of this expansion is delivered in two new types of triggers:
1. DDL TRIGGERS
2. CLR TRIGGERS
Let us know about these two triggers:
2. TYPES OF TRIGGES
1. DDL triggers—DDL triggers fire when DDL statements are executed. These triggers are administrative in nature and are great for auditing and regulating database operations.
2. CLR triggers—Triggers can now be written based on the common language runtime (CLR). The CLR integration in SQL Server 2005 allows triggers to be written using any .NET language (such as C# or Visual Basic .NET) that the CLR supports.
3. “AFTER” TRIGGER
The AFTER trigger executes after a data modification has taken place. This trigger fires after the data modification statement completes but before the statement’s work is committed to the databases. The statement’s work is captured in the transaction log but not committed to the database until the trigger has executed and performed its actions.
Syntax for creating an AFTER trigger is as follows:
CREATE TRIGGER trigger_name
ON table_name
AFTER {INSERT | UPDATE | DELETE}
AS
SQL statements
The AFTER trigger is the default type of DML trigger, so the AFTER keyword is optional.
Example of AFTER Trigger:
CREATE TRIGGER tr_au_upd ON authors
AFTER UPDATE
AS
PRINT ‘TRIGGER OUTPUT: ‘ +CONVERT(VARCHAR(5), @@ROWCOUNT)
+ ‘ rows were updated.’
GO
UPDATE authors
SET au_fname = au_fname
WHERE state = ‘UT’
GO
--TRIGGER OUTPUT: 1 rows were updated.
UPDATE authors
SET au_fname = au_fname
WHERE state = ‘CA’
GO
--TRIGGER OUTPUT: 37 rows were updated.
NOTE::
You need to consider this execution carefully when you design triggers. For example, if you have a constraint and a trigger defined on the same column, any violations to the constraint abort the statement, and the trigger execution does not occur.
- The execution of this command sets the tr_au_upd trigger as the first trigger to fire when
An update happens to the table on which this trigger has been placed.
- If an ALTER statement is executed against the trigger after the trigger order has been defined, the firing order is lost. The sp_settriggerorder procedure must be run again to reestablish the firing order.
NOTE
The following restrictions apply to AFTER triggers:
- AFTER triggers can be placed only on tables, not on views.
- An AFTER trigger cannot be placed on more than one table.
- The text, ntext, and image columns cannot be referenced in the AFTER trigger logic.
4. SYNTAX OF TRIGGER IN DB2
ü DB2's trigger provides an instructive example of the differences.
ü Here is the same trigger definition shown previously for SQL Server, this time using the DB2 syntax:
CREATE TRIGGER NEWORDER
AFTER INSERT ON ORDERS
REFERENCING NEW AS NEW_ORD
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
UPDATE SALESREPS
SET SALES = SALES + NEW_ORD.AMOUNT
WHERE SALESREPS.EMPL_NUM = NEW_ORD.REP;
UPDATE PRODUCTS
SET QTY_ON_HAND = QTY_ON_HAND – NEW_ORD.QTY
WHERE PRODUCTS.MFR_ID = NEW_ORD.MFR
AND PRODUCTS.PRODUCT_ID = NEW_ORD.PRODUCT;
END
- The beginning of the trigger definition includes the same elements as the SQL Server
- definition, but rearranges them.
- It explicitly tells DB2 that the trigger is to be invoked AFTER a new order is inserted into the database.
- DB2 also allows you to specify that the trigger is to be carried out before a triggering action is applied to the database contents.
- This doesn't make sense in this example, because the triggering event is an INSERT
- operation, but it does make sense for UPDATE or DELETE operations.
Prior to SQL Server 2000, SQL Server had after triggers only.
v There is no distinction between AFTER and INSTEAD OF triggers.
v AFTER triggers can be created by using the keyword FOR in place of AFTER.
- A table may have several after triggers for each three table events.
- After triggers is applied to tables only.
- The traditional trigger is an AFTER trigger that fires after the transaction is complete but before the transaction is committed.
- After triggers are useful for the following:
✦ Complex data validation
✦ Enforcing complex business rules
✦ Recording data-audit trails
✦ Maintaining modified date columns
✦ Enforcing custom referential-integrity checks and cascading deletes
Use AFTER triggers when the transaction will likely be accepted because the work is complete
and waiting only for a transaction commit. For this reason, AFTER triggers are excellent
for validating data or enforcing a complex rule.
CREATE TRIGGER TriggerOne ON Person
AFTER Insert
AS
PRINT ‘In the After Trigger’;
With the after trigger enforced, the following code inserts a sample row:
INSERT Person(PersonID, LastName, FirstName, Gender)
VALUES (50, ‘Ebob’, ‘Bill’,’M’);
Result:
In the After Trigger
(1 row(s) affected)
The insert worked and the trigger printed its own version of the “hello world” message.
5. “INSTEAD OF” TRIGGER
- Instead of triggers execute “instead of” (as a substitute for) the submitted transaction, so
- The submitted transaction does not occur.
- It’s as if the presence of an instead of trigger Signals an automatic rollback on submitted transaction.
- As a substitution procedure, each table is limited to only one instead of trigger per table
- event. In addition, instead of triggers may be applied to views as well as tables.
Instead of TRIGGER Example::
Before execution of dml operations
create trigger tgr_stu
on student
instead of insert
as
print 'inseadf of trigger executed'
alter table student disable trigger tgr_stu
insert into student values(1,'ppp',45,56)
6. Disable trigger
Syntax to disable trigger :::
Alter table <tablename>
Disable trigger <triggername>
Example:::::
ALTER TABLE Person
DISABLE TRIGGER TriggerOne
7. Triggers and Security
Only users who are members of the system admin fix server role, or dbowner or
ddladmin fixed database roles, or are the table’s owners, have permission to create, alter,
drop, enable, or disable triggers.
Code within the trigger is executed assuming the security permissions of the owner of the
trigger’s table.
select * from sys.triggers
Virtual table will be maintained internally by database. The internal structure of Virtual Table is as follows::::
TRIGGER VIRTUAL TABLE
EVENT INSERTED DELETED
Insert inserted empty
Delete empty inserted
Update contains the rows after contains the rows before
Updated update
Old data—deleted newdata—inserted into virtual table
8. The following is the example to implement the concept virtual table of trigger::::
Userid
Firstname
Lastname
Deletionloginid
Deletiondate
deltionstatu
Create table users(userid int,
firstname varchar(30),
lastname varchar(30),
deletiondate datetime,
deletionloginid int,deletionstatus char)
select * from users
drop table users
insert into users(userid,firstname,lastname,deletionstatus)
values(3,'rb','pa','u')
drop trigger tgr_users
create trigger tgr_user
on users
for update
as
declare @userid int
declare @deletionstatus char
begin
select @userid=userid,@deletionstatus=deletionstatus
from inserted
if @deletionstatus='d'
begin
update users
set deletiondate=getdate()where userid=@userid
update users
set deletionloginid=@userid
where userid=@userid
end
end
select * from users
update users set deletionstatus='d' where userid=3
select * from users
update users set deletionstatus=’d’ where userid=12
create trigger tgr_user_update
on users
for update
as
if update(deletionstatus)
print 'updated the deletion status values'
--column name only not column value
select * from users
update users set deletionstatus='d' where userid=3
----------------THE END---------------
