SQL Server/T-SQL Tutorial/Trigger/Trigger

Материал из SQL эксперт
Перейти к: навигация, поиск

Check business logic in a trigger

   <source lang="sql">

2> 3> CREATE TABLE Products ( 4> ProductID int IDENTITY (1, 1) NOT NULL , 5> ProductName nvarchar (40) NOT NULL , 6> SupplierID int NULL , 7> CategoryID int NULL , 8> QuantityPerUnit nvarchar (20) NULL , 9> UnitPrice money NULL, 10> UnitsInStock smallint NULL, 11> UnitsOnOrder smallint NULL, 12> ReorderLevel smallint NULL, 13> Discontinued bit NOT NULL 14> ) 15> GO 1> CREATE TRIGGER ProductIsRationed 2> ON Products 3> FOR UPDATE 4> AS 5> IF EXISTS 6> ( 7> SELECT "True" 8> FROM Inserted i 9> JOIN Deleted d 10> ON i.ProductID = d.ProductID 11> WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2 12> AND d.UnitsInStock - i.UnitsInStock > 0 13> ) 14> BEGIN 15> RAISERROR("Cannot reduce stock by more than 50%% at once.",16,1) 16> ROLLBACK TRAN 17> END 18> GO 1> 2> drop TRIGGER ProductIsRationed; 3> GO 1> 2> drop table Products; 3> GO 1></source>


Check record matching in a trigger

   <source lang="sql">

2> CREATE TABLE Activity( 3> ActivityID int IDENTITY (1, 1) NOT NULL, 4> ActivityType int NOT NULL, 5> ActivityDate datetime NOT NULL, 6> ActivityComplete bit NOT NULL 7> ) 8> CREATE TABLE ActivityFootball( 9> ActivityID int NOT NULL, 10> InstantReplay bit NOT NULL, 11> FlagTackle bit NOT NULL, 12> TwoPointPlay bit NOT NULL 13> ) 14> GO 1> 2> CREATE TABLE ActivitySoftball( 3> ActivityID int NOT NULL, 4> NoOfRefs tinyint NOT NULL, 5> DiamondSize tinyint NOT NULL, 6> StealingAllowed bit NOT NULL 7> ) 8> GO 1> 2> 3> CREATE TRIGGER FootballIsExclusiveActivity ON ActivityFootball 4> FOR INSERT, UPDATE 5> AS 6> IF EXISTS( 7> SELECT "True" 8> FROM Inserted i 9> LEFT JOIN Activity a 10> ON i.ActivityID = a.ActivityID 11> WHERE a.ActivityID IS NULL 12> ) 13> BEGIN 14> RAISERROR("Football item Must Have Corresponding Activity",16,1) 15> ROLLBACK TRAN 16> END 17> IF EXISTS( 18> SELECT "True" 19> FROM Inserted i 20> LEFT JOIN ActivitySoftball asb ON i.ActivityID = asb.ActivityID 21> WHERE asb.ActivityID IS NOT NULL 22> ) 23> BEGIN 24> RAISERROR("Matching Softball Record Exists.",16,1) 25> ROLLBACK TRAN 26> END 27> GO 1> 2> drop TRIGGER FootballIsExclusiveActivity ; 3> GO 1> 2> drop table Activity; 3> drop table ActivityFootball; 4> drop table ActivitySoftball; 5> GO</source>


Check @@ROWCOUNT in a trigger

   <source lang="sql">

5> CREATE TABLE employee( 6> id INTEGER NOT NULL PRIMARY KEY, 7> first_name VARCHAR(10), 8> last_name VARCHAR(10), 9> salary DECIMAL(10,2), 10> start_Date DATETIME, 11> region VARCHAR(10), 12> city VARCHAR(20), 13> managerid INTEGER 14> ); 15> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid

---------- ---------- ------------ ----------------------- ---------- -------------------- ----------- 
         1 Jason      Martin          5890.00 2005-03-22 00:00:00.000 North      Vancouver                      3
         2 Alison     Mathews         4789.00 2003-07-21 00:00:00.000 South      Utown                          4
         3 James      Smith           6678.00 2001-12-01 00:00:00.000 North      Paris                          5
         4 Celia      Rice            5567.00 2006-03-03 00:00:00.000 South      London                         6
         5 Robert     Black           4467.00 2004-07-02 00:00:00.000 East       Newton                         7
         6 Linda      Green           6456.00 2002-05-19 00:00:00.000 East       Calgary                        8
         7 David      Larry           5345.00 2008-03-18 00:00:00.000 West       New York                       9
         8 James      Cat             4234.00 2007-07-17 00:00:00.000 West       Regina                         9
         9 Joan       Act             6123.00 2001-04-16 00:00:00.000 North      Toronto                       10

(9 rows affected) 1> 2> CREATE PROCEDURE dbo.usp_employee 3> @Name nvarchar(50), 4> @DeptCount int OUTPUT 5> AS 6> SELECT first_Name 7> FROM employee 8> WHERE city = @Name 9> SELECT @DeptCount = @@ROWCOUNT 10> GO 1> 2> DECLARE @DeptCount int 3> EXEC dbo.usp_employee "Vancouver", 4> @DeptCount OUTPUT 5> PRINT @DeptCount 6> GO first_Name

Jason (1 rows affected) 1 1> 2> ALTER PROCEDURE dbo.usp_employee 3> @GroupName nvarchar(50) 4> AS 5> SELECT first_name 6> FROM Employee 7> WHERE city = @GroupName 8> SELECT @@ROWCOUNT DepartmentCount 9> GO 1> 2> EXEC dbo.usp_Employee "Research and Development" 3> 4> 5> drop procedure dbo.usp_Employee 6> GO first_name

(0 rows affected) DepartmentCount 

             0

(1 rows affected) 1> drop table employee; 2> GO 1></source>


Define variables in a trigger

   <source lang="sql">

4> CREATE TABLE employee 5> ( 6> emp_id varchar(20), 7> fname varchar(20) NOT NULL, 8> minit char(1) NULL, 9> lname varchar(30) NOT NULL, 10> job_id smallint NOT NULL DEFAULT 1, 11> job_lvl tinyint DEFAULT 10, 12> pub_id char(4) NOT NULL DEFAULT ("9952"), 13> hire_date datetime NOT NULL DEFAULT (getdate()) 14> ) 15> GO 1> 2> insert employee values ("1", "Jack", "T", "Lee", 2, 215, "9952", "11/11/89") 3> insert employee values ("2", "Jode", "M", "Devon", 3, 200, "9952", "07/16/91") 4> insert employee values ("3", "Frac", "F", "Chang", 4, 227, "9952", "11/03/90") 5> insert employee values ("4", "Like", "A", "Lebihan", 5, 175, "0736", "06/03/90") 6> insert employee values ("5", "Paul", "X", "Henriot", 5, 159, "0877", "08/19/93") 7> insert employee values ("6", "Sick", "K", "Ottlieb", 5, 150, "1389", "04/05/91") 8> insert employee values ("7", "Rita", "B", "Muller", 5, 198, "1622", "10/09/93") 9> insert employee values ("8", "Mary", "J", "Pontes", 5, 246, "1756", "03/01/89") 10> insert employee values ("9", "Jane", "Y", "Labrune", 5, 172, "9901", "05/26/91") 11> insert employee values ("10","Carl", "F", "Hernadez",5, 211, "9999", "04/21/89") 12> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE jobs( 3> job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, 4> job_desc varchar(50) NOT NULL DEFAULT "New Position - title not formalized yet", 5> min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), 6> max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) 7> ) 8> GO 1> 2> 3> insert jobs values ("Coder", 10, 10) 4> insert jobs values ("Tester", 200, 250) 5> insert jobs values ("Programmer", 175, 225) 6> insert jobs values ("Painter", 175, 250) 7> insert jobs values ("Drawer", 150, 250) 8> insert jobs values ("Editor", 140, 225) 9> insert jobs values ("Manager", 120, 200) 10> insert jobs values ("Manager", 100, 175) 11> insert jobs values ("Representative", 25, 100) 12> insert jobs values ("Designer", 25, 100) 13> 14> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TRIGGER employee_insupd 3> ON employee 4> FOR insert, UPDATE 5> AS 6> 7> declare @min_lvl tinyint, @max_lvl tinyint, @emp_lvl tinyint, @job_id smallint 8> select @min_lvl = min_lvl, @max_lvl = max_lvl, @emp_lvl = i.job_lvl, @job_id = i.job_id 9> from employee e, jobs j, inserted i 10> where e.emp_id = i.emp_id AND i.job_id = j.job_id 11> IF (@job_id = 1) and (@emp_lvl <> 10) 12> begin 13> raiserror ("Job id 1 expects the default level of 10.",16,1) 14> ROLLBACK TRANSACTION 15> end 16> ELSE 17> IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl) 18> begin 19> raiserror ("The level for job_id:%d should be between %d and %d.", 20> 16, 1, @job_id, @min_lvl, @max_lvl) 21> ROLLBACK TRANSACTION 22> end 23> 24> GO 1> 2> drop table employee; 3> drop table jobs; 4> GO 1> 2></source>


Disable a trigger

   <source lang="sql">

4> create table Billings ( 5> BankerID INTEGER, 6> BillingNumber INTEGER, 7> BillingDate datetime, 8> BillingTotal INTEGER, 9> TermsID INTEGER, 10> BillingDueDate datetime , 11> PaymentTotal INTEGER, 12> CreditTotal INTEGER 13> 14> ); 15> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> 2> CREATE TRIGGER tr_Insert_Rollback 3> ON Billings 4> FOR INSERT 5> AS 6> ROLLBACK TRAN 7> PRINT "INSERT statement rolled back." 8> GO 1> 2> 3> DISABLE TRIGGER tr_Insert_Rollback ON Billings 4> GO 1> 2> 3> drop TRIGGER tr_Insert_Rollback; 4> GO 1> 2> drop table Billings; 3> GO</source>


Enable a trigger

   <source lang="sql">

3> create table Billings ( 4> BankerID INTEGER, 5> BillingNumber INTEGER, 6> BillingDate datetime, 7> BillingTotal INTEGER, 8> TermsID INTEGER, 9> BillingDueDate datetime , 10> PaymentTotal INTEGER, 11> CreditTotal INTEGER 12> 13> ); 14> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> 2> CREATE TRIGGER tr_Insert_Rollback 3> ON Billings 4> FOR INSERT 5> AS 6> ROLLBACK TRAN 7> PRINT "INSERT statement rolled back." 8> GO 1> 2> ENABLE TRIGGER tr_Insert_Rollback ON Billings 3> GO 1> drop TRIGGER tr_Insert_Rollback; 2> GO 1> 2> drop table Billings; 3> GO</source>


exits if the price column has not been updated.

   <source lang="sql">

3> 4> CREATE TABLE titles( 5> title_id varchar(20), 6> title varchar(80) NOT NULL, 7> type char(12) NOT NULL, 8> pub_id char(4) NULL, 9> price money NULL, 10> advance money NULL, 11> royalty int NULL, 12> ytd_sales int NULL, 13> notes varchar(200) NULL, 14> pubdate datetime NOT NULL 15> ) 16> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TRIGGER myTrigger ON titles 3> FOR UPDATE 4> AS 5> DECLARE @chvMsg VARCHAR(255), 6> @chvTitleID VARCHAR(6), 7> @mnyOldPrice MONEY, 8> @mnyNewPrice MONEY 9> DECLARE myCursor CURSOR 10> FOR 11> SELECT d.title_id, d.price, i.price 12> FROM deleted d INNER JOIN inserted i ON d.title_id = i.title_id 13> IF update(price) 14> BEGIN 15> OPEN myCursor 16> FETCH NEXT FROM myCursor INTO 17> @chvTitleID, @mnyOldPrice, @mnyNewPrice 18> WHILE (@@fetch_status <> -1) 19> BEGIN 20> SELECT @chvMsg = "The price of title " + @chvTitleID 21> + " has changed from" 22> + " " + CONVERT(VARCHAR(10), @mnyOldPrice) 23> + " to " + CONVERT(VARCHAR(10), @mnyNewPrice) 24> + " on " + 25> CONVERT(VARCHAR(30), getdate()) + "."EXEC master..xp_sendmail "Colleen", @chvMsg 26> FETCH NEXT FROM myCursor 27> INTO @chvTitleID, @mnyOldPrice, @mnyNewPrice 28> SELECT @chvMsg = "" 29> END 30> DEALLOCATE myCursor 31> END 32> RETURN 33> GO 1> 2> drop trigger myTrigger; 3> GO 1> 2> drop table titles; 3> GO 1> 2></source>


INSTEAD OF INSERT trigger for a table

   <source lang="sql">

2> CREATE TABLE MyTable( 3> ID int NOT NULL PRIMARY KEY, 4> Descr char (5) NOT NULL 5> ) 6> GO 1> CREATE TRIGGER tri_MyTable ON MyTable INSTEAD OF INSERT 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> UPDATE F -- rows that already exist 6> SET 7> Descr = I.Descr 8> FROM 9> inserted AS I 10> JOIN 11> MyTable AS F ON F.ID = I.ID 12> INSERT MyTable -- new rows 13> SELECT 14> ID, 15> Descr 16> FROM 17> inserted AS I 18> WHERE NOT EXISTS 19> ( 20> SELECT 21> * 22> FROM 23> MyTable AS F 24> WHERE 25> F.ID = I.ID 26> ) 27> GO 1> INSERT MyTable (ID, Descr) VALUES (1, "a") 2> INSERT MyTable (ID, Descr) VALUES (2, "b") 3> INSERT MyTable (ID, Descr) VALUES (3, "c") 4> INSERT MyTable (ID, Descr) VALUES (1, "d") 5> INSERT MyTable (ID, Descr) VALUES (1, "e") 6> 7> drop table MyTable 8> GO (0 rows affected) (1 rows affected) (0 rows affected) (1 rows affected) (0 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1></source>


RAISERROR in trigger

   <source lang="sql">

5> 6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> create TRIGGER ProductIsRationed 3> ON Products 4> FOR UPDATE 5> AS 6> IF UPDATE(UnitsInStock) 7> BEGIN 8> IF EXISTS 9> ( 10> SELECT "True" 11> FROM Inserted i 12> JOIN Deleted d 13> ON i.ProductID = d.ProductID 14> WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2 15> AND d.UnitsInStock - i.UnitsInStock > 0 16> ) 17> BEGIN 18> RAISERROR("Cannot reduce stock by more than 50%% at once.",16,1) 19> ROLLBACK TRAN 20> END 21> END 22> GO 1> 2> drop TRIGGER ProductIsRationed; 3> GO 1> 2> drop table Products; 3> GO</source>


Rollback transaction in a trigger

   <source lang="sql">

5> 6> CREATE TABLE Products ( 7> ProductID int NOT NULL , 8> ProductName nvarchar (40) NOT NULL , 9> SupplierID int NULL , 10> CategoryID int NULL , 11> QuantityPerUnit nvarchar (20) NULL , 12> UnitPrice money NULL, 13> UnitsInStock smallint NULL, 14> UnitsOnOrder smallint NULL, 15> ReorderLevel smallint NULL, 16> Discontinued bit NOT NULL 17> ) 18> GO 1> INSERT Products VALUES(1,"F",15,4,"10 - 999 g pkgs.",61.5,66,6,6,6) 2> INSERT Products VALUES(2,"M",14,4,"24 - 888 g pkgs.",34.8,74,7,7,7) 3> INSERT Products VALUES(3,"R",17,8,"24 - 777 g jars",17,171,0,5,0) 4> INSERT Products VALUES(4,"L",4,7,"5 kg pkg.",10,4,20,5,0) 5> INSERT Products VALUES(5,"R",12,1,"24 - 0.5 l bottles",1.23,445,0,25,0) 6> INSERT Products VALUES(6,"L",23,1,"500 ml",18,57,1,20,0) 7> INSERT Products VALUES(7,"O",12,2,"12 boxes",13,23,0,15,0) 8> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> create TRIGGER ProductIsRationed 3> ON Products 4> FOR UPDATE 5> AS 6> IF UPDATE(UnitsInStock) 7> BEGIN 8> IF EXISTS 9> ( 10> SELECT "True" 11> FROM Inserted i 12> JOIN Deleted d 13> ON i.ProductID = d.ProductID 14> WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2 15> AND d.UnitsInStock - i.UnitsInStock > 0 16> ) 17> BEGIN 18> RAISERROR("Cannot reduce stock by more than 50%% at once.",16,1) 19> ROLLBACK TRAN 20> END 21> END 22> GO 1> 2> drop TRIGGER ProductIsRationed; 3> GO 1> 2> drop table Products; 3> GO</source>


Table for INSTEAD OF Trigger for Logical Deletes

   <source lang="sql">

4> 5> CREATE TABLE MyTable 6> ( 7> ID int NOT NULL PRIMARY KEY, 8> del char (1) NOT NULL DEFAULT "N" 9> ) 10> GO 1> 2> --INSTEAD OF Trigger for Logical Deletes 3> CREATE TRIGGER trd_MyTable ON MyTable INSTEAD OF DELETE 4> AS 5> IF @@ROWCOUNT = 0 6> RETURN 7> UPDATE M 8> SET 9> del = "Y" 10> FROM 11> MyTable AS M 12> JOIN 13> deleted AS D ON D.ID = M.ID 14> GO 1> 2> drop table mytable 3> GO</source>


The syntax of the CREATE TRIGGER statement

   <source lang="sql">

CREATE TRIGGER trigger_name ON {table_name|view_name} [WITH ENCRYPTION] {FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] AS sql_statements</source>


Trigger Scripts for Cascading DELETEs

   <source lang="sql">

3> 4> CREATE TABLE Parent( 5> ID int NOT NULL PRIMARY KEY 6> ) 7> GO 1> CREATE TABLE Child( 2> ID int NOT NULL PRIMARY KEY 3> REFERENCES Parent (ID) 4> ON DELETE CASCADE 5> ) 6> GO 1> CREATE TABLE GrandChild( 2> ID int NOT NULL PRIMARY KEY 3> REFERENCES Child (ID) 4> ON DELETE CASCADE 5> ) 6> GO 1> INSERT Parent VALUES (1) 2> INSERT Parent VALUES (2) 3> INSERT Parent VALUES (3) 4> INSERT Parent VALUES (4) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> INSERT Child VALUES (1) 2> INSERT Child VALUES (2) 3> INSERT Child VALUES (3) 4> INSERT Child VALUES (4) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> INSERT GrandChild VALUES (1) 2> INSERT GrandChild VALUES (2) 3> INSERT GrandChild VALUES (3) 4> INSERT GrandChild VALUES (4) 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> CREATE TRIGGER trd_Parent ON Parent AFTER DELETE 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> PRINT "Inside Parent trigger." 6> GO 1> CREATE TRIGGER trd_Child ON Child AFTER DELETE 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> PRINT "Inside Child trigger." 6> GO 1> CREATE TRIGGER trd_GrandChild ON GrandChild AFTER DELETE 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> PRINT "Inside GrandChild trigger." 6> GO 1> --Firing the Cascaded DELETE 2> DELETE Parent 3> WHERE 4> ID BETWEEN 2 AND 3 5> 6> drop trigger trd_parent 7> drop trigger trd_child 8> drop trigger trd_grandchild 9> drop table grandchild 10> drop table child 11> drop table parent 12> GO Inside GrandChild trigger. Inside Child trigger. (2 rows affected) Inside Parent trigger.</source>


Update table in a trigger

   <source lang="sql">

3> CREATE TABLE Customers ( 4> CustomerID nchar (5) NOT NULL , 5> CompanyName nvarchar (40) NOT NULL , 6> ContactName nvarchar (30) NULL , 7> ContactTitle nvarchar (30) NULL , 8> Address nvarchar (60) NULL , 9> City nvarchar (15) NULL , 10> Region nvarchar (15) NULL , 11> PostalCode nvarchar (10) NULL , 12> Country nvarchar (15) NULL , 13> Phone nvarchar (24) NULL , 14> Fax nvarchar (24) NULL 15> ) 16> GO 1> 2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111") 3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL) 4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444") 5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55") 6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777") 7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32") 8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99") 9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41") 10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745") 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE OrderDetails ( 4> OrderID int NOT NULL , 5> ProductID int NOT NULL , 6> UnitPrice money NOT NULL DEFAULT (0), 7> Quantity smallint NOT NULL DEFAULT (1), 8> Discount real NOT NULL DEFAULT (0) 9> ) 10> GO 1> INSERT OrderDetails VALUES(10248,11,14,12,0) 2> INSERT OrderDetails VALUES(10248,42,9.8,10,0) 3> INSERT OrderDetails VALUES(10248,72,34.8,5,0) 4> INSERT OrderDetails VALUES(10249,14,18.6,9,0) 5> INSERT OrderDetails VALUES(10249,51,42.4,40,0) 6> INSERT OrderDetails VALUES(10250,41,7.7,10,0) 7> INSERT OrderDetails VALUES(10250,51,42.4,35,0.15) 8> INSERT OrderDetails VALUES(10250,65,16.8,15,0.15) 9> INSERT OrderDetails VALUES(10251,22,16.8,6,0.05) 10> INSERT OrderDetails VALUES(10251,57,15.6,15,0.05) 11> go (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> CREATE TABLE Orders ( 3> OrderID int NOT NULL , 4> CustomerID nchar (5) NULL , 5> EmployeeID int NULL , 6> OrderDate datetime NULL , 7> RequiredDate datetime NULL , 8> ShippedDate datetime NULL , 9> ShipVia int NULL , 10> Freight money NULL DEFAULT (0), 11> ShipName nvarchar (40) NULL , 12> ShipAddress nvarchar (60) NULL , 13> ShipCity nvarchar (15) NULL , 14> ShipRegion nvarchar (15) NULL , 15> ShipPostalCode nvarchar (10) NULL , 16> ShipCountry nvarchar (15) NULL 17> ) 18> GO 1> 2> 3> 4> ALTER TABLE Customers 5> ADD CurrentBalance money NOT NULL 6> CONSTRAINT CurrentBalanceDefault 7> DEFAULT 0 WITH VALUES 8> GO 1> CREATE TRIGGER myTrigger 2> ON OrderDetails 3> FOR INSERT, UPDATE, DELETE 4> AS 5> UPDATE c 6> SET c.CurrentBalance = c.CurrentBalance + i.UnitPrice * i.Quantity * 7> (1 - Discount) 8> FROM Customers c 9> JOIN Orders o 10> ON c.CustomerID = o.CustomerID 11> JOIN Inserted i 12> ON o.OrderID = i.OrderID 13> UPDATE c 14> SET c.CurrentBalance = c.CurrentBalance - d.UnitPrice * d.Quantity * 15> (1 - d.Discount) 16> FROM Customers c 17> JOIN Orders o 18> ON c.CustomerID = o.CustomerID 19> JOIN Deleted d 20> ON o.OrderID = d.OrderID 21> GO 1> 2> drop TRIGGER myTrigger; 3> GO 1> 2> drop table Customers; 3> drop table Orders; 4> drop table OrderDetails; 5> GO</source>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Trigger/Trigger&oldid=6824»