Use of SQL triggers

Use of SQL triggers

Triggers: A special type of stored procedure that is not invoked directly by the user or manually, but is triggered by events, such as when working on a table (insert,delete,update). When a trigger is created, it is defined so that it is executed when a specific type of data modification is made to a particular table or column.

Advantages of using triggers: 1. Triggers are automatically provided on the MySQL server

2. Triggers can query multiple tables and can contain complex SQL statements.

Take a beef brisket. An example on the news system briefly describes the use of triggers:

Requirements: Delete News categories and delete news content and news comments under the category of news, which involves three of table operations (Category, News, Comment), if s QL statements are difficult to implement.

/*
* How to create after DML triggers
* How to create a instead of DML trigger
* How to create a DDL trigger
* How to modify and delete existing triggers
* How to enable and disable triggers
* How to restrict trigger nesting, set trigger order, and control recursion
* How to view trigger elements
*/
--1, DML triggers
--A, after DML triggers are executed after a successful completion of the insert,update and delete modifications to the table
--Grammar:

/*
Trigger on the INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [schema_name.] Trigger_name
On {table | view}
[With <dml_trigger_option> [,... N]]
{for | After | INSTEAD of}
{[INSERT] [,] [UPDATE] [,] [DELETE]}
[With APPEND]
[Not for REPLICATION]
as {sql_statement [;] [... n] | EXTERNAL NAME <method specifier [;] >}

<dml_trigger_option>:: =
[Encryption]
[EXECUTE as Clause]
*/

--use triggers to track insertions and deletions of rows of production.productinventory tables

--keep track of all inserts, updates, and deletes

CREATE TABLE Production.productinventoryaudit
(
ProductID int NOT NULL,
LocationID smallint NOT NULL,
Shelf nvarchar (ten) is not NULL,
Bin tinyint NOT NULL,
Quantity smallint NOT NULL,
ROWGUID uniqueidentifier NOT NULL,
ModifiedDate datetime NOT NULL,
Insorupd char (1) NOT NULL
)

Go

--Create a trigger to populate the Production.productinventoryaudit table

Create Trigger Production.trg_uid_productinventoryaudit
On Production.productinventory
After Insert,delete
As

SET NOCOUNT ON--"affected number of rows" message returned to the invoked application when a masked trigger is triggered

--Insert Row

Insert Production.productinventoryaudit
(PRODUCTID,LOCATIONID,SHELF,BIN,QUANTITY,ROWGUID,MODIFIEDDATE,INSORUPD)
Select Distinct i.productid,i.locationid,i.shelf,i.bin,i.quantity,i.rowguid,getdate (), ' I '
From inserted I

--Deleted rows

Insert Production.productinventoryaudit
(PRODUCTID,LOCATIONID,SHELF,BIN,QUANTITY,ROWGUID,MODIFIEDDATE,INSORUPD)
Select Distinct d.productid,d.locationid,d.shelf,d.bin,d.quantity,d.rowguid,getdate (), ' d '
From deleted D
Go

--Insert a new row
Insert production. ProductInventory
(productid,locationid,shelf,bin,quantity) VALUES (316,6, ' A ', 4,22)

--Delete a row
Delete production.productinventory where productid=316 and locationid=6

--Inspection Audit table
Select Productid,locationid,insorupd from Production.productinventoryaudit

--select * from production. ProductInventory
--select * from Production.Product
--b, creating instead of DML triggers
The execution of the--instead of triggers replaces the original data modification action that triggers the trigger, and allows for both the table and the view
--typically used to handle data modification operations for views that do not allow data modification

--Example: Create a new table to save the HumanResources.Department table "Awaiting approval pending approval"
--yes. These are the new departments that require the manager's approval to join the formal form. Create a view to display all of the two tables
--approved and pending approval departments, and then create a instead of trigger on the view, causing the inserted rows
-will be transferred to the new approval table instead of the HumanResources.Department table:

--Create a Department approval table

CREATE TABLE Humanresources.departmentapproval
(
Name nvarchar NOT null unique,
GroupName nvarchar (m) not NULL,
ModifiedDate datetime NOT NULL default GETDATE ()
)
Go

--Create a view to view approved and pending departments
CREATE VIEW Humanresources.vw_department
As
Select Name,groupname,modifieddate, ' Approved ' Status
From HumanResources.Department
Union
Select Name,groupname,modifieddate, ' Pending Approval ' stuatus
From Humanresources.departmentapproval

Go

--Create instead on triggers on new view
Create Trigger Humanresources.trg_vw_department
On Humanresources.vw_department
Instead of insert
As
SET NOCOUNT ON
Insert Humanresources.departmentapproval (name,groupname)
Select I.name,i.groupname from Inserted i
where I.name not in (select Name from Humanresources.departmentapproval)
Go

--inserting rows into the view
Insert Humanresources.vw_department
(name,groupname) VALUES (' Print Production ', ' manufacturing ')

--Check the contents of the view

Select Status,name
From Humanresources.vw_department where groupname= ' manufacturing '

--select * from HumanResources.Department
--select * from Humanresources.departmentapproval

--C, using DML triggers and transactions

Alter TRIGGER PRODUCTION.TRG_UID_PRODUCTINVENTORYAUDIT1
On Production.productinventory after Insert,delete
As

SET NOCOUNT ON
If exists (select shelf from inserted where shelf= ' A ')

Begin

print ' shelf ' A ' is closed for new inventory. '
Rollback
End

--Inserted rows
Insert Production.productinventoryaudit
(PRODUCTID,LOCATIONID,SHELF,BIN,QUANTITY,ROWGUID,MODIFIEDDATE,INSORUPD)
SELECT distinct i.productid,i.locationid,i.shelf,i.bin,i.quantity,i.rowguid,getdate (), ' I '
From inserted I

--Deleted rows

Insert Production.productinventoryaudit
(
Productid,locationid,shelf,bin,quantity,rowguid,modifieddate,insorupd
)
Select D.productid,d.locationid,d.shelf,d.bin,d.quantity,d.rowguid,getdate (), ' d '
From deleted D

if exists
(
Select Quantity from deleted where quantity>0
)
Begin
print ' You can't delete a certain number of rows! '
Rollback
End
Go

--use shelf ' A ' to insert A new row to test
Insert Production.productinventory (productid,locationid,shelf,bin,quantity)
VALUES (316,6, ' A ', 4,22)

--Using an explicit transaction demo two deletes

BEGIN TRANSACTION
--Delete 0 number of rows
Delete production.productinventory where productid=853 and locationid=7

--Delete a number of rows other than 0
Delete production.productinventory where productid=999 and locationid=60

Commit TRANSACTION

--because the trigger initiated a rollback, the external transaction ended, so the same transaction was not deleted

Select Productid,locationid from Production.productinventory
Where (productid=853 and locationid=7) or (productid=999 and locationid=60)

--d, viewing the metadata of a DML trigger

--demonstrates viewing information about triggers in the current database tutorial

Select object_name (parent_id) table_or_viewnm,
Name triggernm,is_instead_of_trigger,is_disabled
From Sys.triggers
where parent_class_desc= ' Object_or_column '
Order by object_name (parent_id), name

--Shows a T-SQL definition for a trigger that you can query for sys.sql_modules
Select O.name,m.definition
From sys.sql_modules m
INNER JOIN sys.objects o
On m.object_id=o.object_id where o.type= ' TR '

--2, DDL triggers

--sql Server2005 The introduction of DDL triggers is a response to server live database events, not table data
--Modify
--Grammar:
/*
Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
On {All SERVER | DATABASE}
[With <ddl_trigger_option> [,... N]]
{for | After} {event_type | event_group} [,... N]
as {sql_statement [;] [... n] | EXTERNAL NAME < method specifier > [;]}

<ddl_trigger_option>:: =
[Encryption]
[EXECUTE as Clause]

<method_specifier>:: =
Assembly_name.class_name.method_name
*/
--A, creating DDL triggers for auditing server-level events

--Creating DDL triggers
Use master
Go
--No new logins are allowed on the SQL instance

Create trigger Srv_trg_restricnewlogins on the all server
For Create_login
As
print ' Prohibit creation of new logins '
Rollback
Go

--Trying to add a new SQL login:
Create Login Joes with password= ' A235921 '
Go
--b, creating DDL triggers for auditing database-level events
--Create an audit table
CREATE TABLE dbo. Changeattempt
(
EventData XML NOT NULL,
Attemptdate datetime NOT NULL default GETDATE (),
Dbuser char (NOT NULL)
)
Go
--Create a database DDL trigger to track index operations, insert event data into newly created tables:

Create Trigger Db_trg_restrictindexchanges
On database
For Create_index,alter_index,drop_index
As
SET NOCOUNT ON
--eventdata () function returns server and data event information in XML format
Insert dbo. Changeattempt (Eventdata,dbuser) VALUES (EventData (), User)
Go

--Create a real index in the database

Create nonclustered index in_changeattempt_dbuser on dbo. Changeattempt (Dbuser)
Go
SELECT * FROM dbo. Changeattempt

--C, viewing DDL trigger meta data

--Displays DDL triggers in the current database
Select Name triggernm,is_disabled
From Sys.triggers
where parent_class_desc= ' DATABASE '
Order by object_name (parent_id), name

--Querying server-level triggers data

Select Name,s.type_desc sql_or_clr,
Is_disabled,e.type_desc firingevents
From Sys.server_triggers S
Inner JOIN sys.server_trigger_events E on
s.object_id=e.object_id

--T-SQL definition of query database-scoped DDL triggers

Select T.name,m.definition
From Sys.triggers as T
Inner join sys.sql_modules m on t.object_id=m.object_id
where t.parent_class_desc= ' database '

--To display DDL triggers within the service period range

Select T.name,m.definition
From Sys.server_sql_modules m
INNER JOIN Sys.server_triggers T on
m.object_id=t.object_id

--3, managing triggers

--Modifying triggers

--Modify the trigger, this time will not restrict the user to create a new login, but instead allow the logon name event, followed by a warning and the audit table to insert
Alter TRIGGER Srv_trg_restricnewlogins
On the all server
For Create_login
As
SET NOCOUNT ON
print ' You create login will be monitored '
Insert AdventureWorks.dbo.ChangeAttempt
(Eventdata,dbuser) VALUES (EventData (), user)

Go

--Enable and disable triggers

Create Trigger Humanresources.trg_department
On HumanResources.Department
After insert
As
Print N ' triggers are activated '
Go

Disable Trigger Humanresources.trg_department
On HumanResources.Department
--Because the trigger is disabled, the following insert does not return print messages after execution

Insert HumanResources.Department (Name,groupname) VALUES (' construction ', ' Building Services ')

Go

--enable the trigger command to enable triggers
Enable Trigger Humanresources.trg_department
On HumanResources.Department

--Insert Again
Insert HumanResources.Department
(Name,groupname)
VALUES (' Cleaning1 ', ' Building Services ')

--Restricting trigger nesting

/*
The action that is executed after the trigger triggers another trigger, and the trigger then touches
The trigger nesting occurs when another trigger is sent.
The maximum nesting level for SQL Server 2005 is 32 tiers
*/

--Disable and enable trigger nesting

Use master
Go

--No nesting
EXEC sp_configure ' nested triggers ', 0
Reconfigure with override--because the server option contains the current configuration and run configuration, this command updates the Run-time value to take effect immediately
Go

--Enable nesting

EXEC sp_configure ' nested trigger ', 1
Reconfigure with override
Go

--Control trigger recursion

/*
This trigger nesting is considered recursive if the behavior that is executed after the trigger triggers triggers the same table trigger. When triggers trigger the impact
Other tables, recursion can occur if the trigger also affects the original table, causing the original trigger to fire again
*/

--Enable and disable recursive triggers

ALTER DATABASE AdventureWorks-allow recursive triggers in the database
Set Recursive_triggers on

--View Database settings

Select is_recursive_triggers_on
From sys.databases

where Name= ' AdventureWorks '

--Prevent recursion
ALTER DATABASE AdventureWorks
Set Recursive_triggers off

--View Database settings
Select is_recursive_triggers_on
From sys.databases
where Name= ' AdventureWorks '

--Set Trigger trigger order

--Create a test table and add 3 DML inserts to the trigger, and then use Sp_settrigger to define the trigger order

CREATE TABLE dbo. Testtriggerorder
(
TestID int NOT NULL
)
Go

Create Trigger Dbo.trg_i_testtriggerorder
ON dbo. Testtriggerorder
After insert
As
Print N ' I'll be the first to trigger '
Go

Create Trigger Dbo.trg_i_testtriggerorder2
ON dbo. Testtriggerorder
After insert
As
Print N ' I will be the last to be triggered '
Go
Create Trigger Dbo.trg_i_testtriggerorder3
ON dbo. Testtriggerorder
After insert
As
Print N ' I will not be the first and not the last to be triggered '
Go

exec sp_settriggerorder ' trg_i_testtriggerorder ', ' I ', ' INSERT '
exec sp_settriggerorder ' trg_i_testtriggerorder2 ', ' last ', ' INSERT '

Insert dbo. Testtriggerorder values (2);
Go

--Deleting triggers

--Deleting a DML trigger
Drop Trigger Dbo.trg_i_testtriggerorder
--Delete multiple triggers

Drop Trigger Dbo.trg_i_testtriggerorder2,dbo.trg_i_testtriggerorder3

--Removing DDL triggers

Drop Trigger Db_trg_restrictindexchanges

Instance

Use [newssystem]go/****** object: 
Trigger [dbo].[ trigcategorydelete]   
Script date:06/24/2011 19:31:14 ******/
SET ansi_nulls ONGOSET QUOTED_ IDENTIFIER ongo--=============================================--
 author: Li Yingjie    
< author,,name>--Create date:2011-06-22--description:   
Delete category triggers--
====================== =======================
ALTER TRIGGER [dbo]. [trigcategorydelete]  
on  [dbo].[ category]  
Instead of Deleteas begin 
Declare @caId int  select @caId =id from deleted    
--delete comment
 delete comment where newsId in (select newsid  from news where caid= @caId)   br>--Delete news   Delete information where caid= @caId  
--delete category   Delete category where id= @caId    &nb Sp
 set NOCOUNT on;
End