SQL Server/T-SQL Tutorial/Trigger/Trigger for after

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

An after-update trigger

   <source lang="sql">

9> create table Billings ( 10> BillingID INTEGER, 11> BankerID INTEGER, 12> BillingNumber INTEGER, 13> BillingDate datetime, 14> BillingTotal INTEGER, 15> TermsID INTEGER, 16> BillingDueDate datetime , 17> PaymentTotal INTEGER, 18> CreditTotal INTEGER 19> 20> ); 21> GO 1> 2> INSERT INTO Billings VALUES (1,1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2,2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3,3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4,4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5,5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6,6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7,7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8,8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9,9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0,0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> create table Bankers( 4> BankerID Integer, 5> BankerName VARCHAR(20), 6> BankerContactLName VARCHAR(20), 7> BankerContactFName VARCHAR(20), 8> BankerCity VARCHAR(20), 9> BankerState VARCHAR(20), 10> BankerZipCode VARCHAR(20), 11> BankerPhone VARCHAR(20) 12> ) 13> GO 1> 2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111"); 3> GO (1 rows affected) 1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222"); 2> GO (1 rows affected) 1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333"); 2> GO (1 rows affected) 1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444"); 2> GO (1 rows affected) 1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555"); 2> GO (1 rows affected) 1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666"); 2> GO (1 rows affected) 1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777"); 2> GO (1 rows affected) 1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888"); 2> GO (1 rows affected) 1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999"); 2> GO (1 rows affected) 1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 2> GO (1 rows affected) 1> 2> 3> 4> CREATE TRIGGER Billings_UPDATE 5> ON Billings 6> AFTER UPDATE 7> AS 8> IF EXISTS 9> (SELECT * 10> FROM Deleted JOIN Billings 11> ON Deleted.BillingID = Billings.BillingID 12> WHERE Deleted.PaymentTotal <> Billings.PaymentTotal) 13> BEGIN 14> IF EXISTS 15> (SELECT * 16> FROM Billings JOIN 17> (SELECT BillingID, SUM(BillingLineItemAmount) AS SumOfBillings 18> FROM BillingLineItems 19> GROUP BY BillingID) AS LineItems 20> ON Billings.BillingID = LineItems.BillingID 21> WHERE (Billings.BillingTotal <> LineItems.SumOfBillings) AND 22> (LineItems.BillingID IN (SELECT BillingID FROM Deleted))) 23> BEGIN 24> RAISERROR("Correct line item amounts before posting payment.",1,1) 25> ROLLBACK TRAN 26> END 27> END 28> GO 1> 2> UPDATE Billings 3> SET PaymentTotal = 662 4> WHERE BillingID = 100 5> GO (0 rows affected) 1> 2> drop TRIGGER Billings_UPDATE; 3> GO 1> 2> 3> drop table Bankers; 4> GO 1> 2> 3> drop table Billings; 4> GO</source>


One table with two after-delete triggers

   <source lang="sql">

3> CREATE TABLE test_trigger 4> (col1 int, 5> col2 char(6) ) 6> GO 1> INSERT INTO test_trigger VALUES (1, "First") 2> INSERT INTO test_trigger VALUES (2, "Second") 3> INSERT INTO test_trigger VALUES (3, "Third") 4> INSERT INTO test_trigger VALUES (4, "Fourth") 5> INSERT INTO test_trigger VALUES (5, "Fifth") 6> GO 1> 2> CREATE TRIGGER delete_test 3> ON test_trigger AFTER DELETE 4> AS 5> PRINT "You just deleted a row!" 6> GO 1> 2> ALTER TRIGGER delete_test 3> ON test_trigger AFTER DELETE 4> AS 5> IF @@ROWCOUNT = 0 RETURN 6> PRINT "You just deleted a row!" 7> GO 1> 2> DELETE test_trigger 3> WHERE col1 = 0 4> GO 1> 2> drop table test_trigger; 3> GO 1></source>


Rolling Back in an AFTER 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> drop TRIGGER tr_Insert_Rollback; 3> GO 1> 2> drop table Billings; 3> GO</source>


Using an AFTER Trigger to Remove Time from a datetime Column

   <source lang="sql">

5> CREATE TABLE DateTable( 6> ID int NOT NULL IDENTITY (1, 1) 7> PRIMARY KEY, 8> Txt char (10) NOT NULL, 9> EntryDate datetime NOT NULL 10> ) 11> GO 1> CREATE TRIGGER t_DateTable ON DateTable AFTER INSERT, UPDATE 2> AS 3> IF @@ROWCOUNT = 0 4> RETURN 5> IF UPDATE (EntryDate) 6> UPDATE D 7> SET 8> EntryDate = CONVERT (char (10), I.EntryDate, 112) 9> FROM 10> inserted I 11> JOIN 12> DateTable D ON D.ID = I.ID 13> GO 1> 2> drop trigger t_DateTable 3> drop table dateTable 4> GO</source>