SQL Server/T-SQL Tutorial/Trigger/Utility trigger — различия между версиями

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

Текущая версия на 13:24, 26 мая 2010

A CREATE TRIGGER statement that corrects mixed-case state names

   <source lang="sql">

8> create table Bankers( 9> BankerID Integer, 10> BankerName VARCHAR(20), 11> BankerContactLName VARCHAR(20), 12> BankerContactFName VARCHAR(20), 13> BankerCity VARCHAR(20), 14> BankerState VARCHAR(20), 15> BankerZipCode VARCHAR(20), 16> BankerPhone VARCHAR(20) 17> ) 18> 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> CREATE TRIGGER Bankers_INSERT_UPDATE 3> ON Bankers 4> AFTER INSERT,UPDATE 5> AS 6> UPDATE Bankers 7> SET BankerState = UPPER(BankerState) 8> WHERE BankerID IN (SELECT BankerID FROM Inserted) 9> GO 1> 2> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000"); 3> GO (1 rows affected) 1> drop trigger Bankers_INSERT_UPDATE; 2> GO 1> 2> drop table Bankers; 3> GO 1> 2></source>


A script that creates AFTER triggers to maintain referential integrity

   <source lang="sql">

4> 5> create table Billings ( 6> BankerID INTEGER, 7> BillingNumber INTEGER, 8> BillingDate datetime, 9> BillingTotal INTEGER, 10> TermsID INTEGER, 11> BillingDueDate datetime , 12> PaymentTotal INTEGER, 13> CreditTotal INTEGER 14> 15> ); 16> 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> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (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> CREATE TRIGGER Banker_UPDATE_DELETE_RI 4> ON Bankers 5> AFTER DELETE,UPDATE 6> AS 7> IF EXISTS (SELECT * FROM Deleted JOIN Billings 8> ON Deleted.BankerID = Billings.BankerID) 9> BEGIN 10> RAISERROR("BankerID in use.",1,1) 11> ROLLBACK TRAN 12> END 13> GO 1> 2> CREATE TRIGGER Billing_INSERT_UPDATE_RI 3> ON Billings 4> AFTER INSERT,UPDATE 5> AS 6> IF NOT EXISTS (SELECT * FROM Bankers 7> WHERE BankerID IN (SELECT BankerID FROM Inserted)) 8> BEGIN 9> RAISERROR("Invalid BankerID.",1,1) 10> ROLLBACK TRAN 11> END 12> GO 1> 2> DELETE Bankers 3> WHERE BankerID = 34 4> GO (0 rows affected) 1> 2> drop TRIGGER Banker_UPDATE_DELETE_RI; 3> GO 1> 2> drop table Bankers; 3> GO 1> 2> 3> drop table Billings; 4> GO</source>


Auditing Triggers

   <source lang="sql">

4> 5> 6> CREATE TABLE sales( 7> stor_id char(4) NOT NULL, 8> ord_num varchar(20) NOT NULL, 9> ord_date datetime NOT NULL, 10> qty smallint NOT NULL, 11> payterms varchar(12) NOT NULL, 12> title_id varchar(80) 13> ) 14> GO 1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1") 2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1") 3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2") 4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3") 5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3") 6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2") 7> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> CREATE TABLE SalesArchive ( 2> stor_id CHAR(4) NOT NULL, 3> ord_num VARCHAR(20) NOT NULL, 4> ord_date DATETIME NOT NULL, 5> qty SMALLINT NOT NULL, 6> payterms VARCHAR(12) NOT NULL, 7> title_id CHAR(4), 8> ) 9> GO 1> 2> 3> CREATE TRIGGER trSales_Del ON sales 4> FOR DELETE 5> AS 6> INSERT SalesArchive 7> SELECT stor_id, ord_num, ord_date, qty, payterms, title_id 8> FROM deleted 9> GO 1> 2> 3> drop trigger trSales_Del; 4> GO 1> 2> drop table SalesArchive; 3> GO 1> drop table sales; 2> GO</source>


Creating the UpdateLog table and an update trigger on the authors table.

   <source lang="sql">

4> 5> 6> CREATE TABLE authors( 7> au_id varchar(11), 8> au_lname varchar(40) NOT NULL, 9> au_fname varchar(20) NOT NULL, 10> phone char(12) NOT NULL DEFAULT ("UNKNOWN"), 11> address varchar(40) NULL, 12> city varchar(20) NULL, 13> state char(2) NULL, 14> zip char(5) NULL, 15> contract bit NOT NULL 16> ) 17> GO 1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1) 2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1) 3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1) 4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1) 5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1) 6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1) 7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1) 8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0) 9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0) 10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1) 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 UpdateLog 3> ( 4> table_name VARCHAR(30) NOT NULL, 5> column_name VARCHAR(30) NOT NULL, 6> updated_by VARCHAR(30) NOT NULL, 7> updated_when DATETIME NOT NULL 8> ) 9> GO 1> 2> CREATE TRIGGER trAuthors_Upd ON Authors 3> FOR UPDATE 4> AS 5> DECLARE @intRowCount int 6> SELECT @intRowCount = @@RowCount 7> IF @intRowCount > 0 8> BEGIN 9> IF UPDATE(au_id) 10> INSERT UpdateLog VALUES ("authors", "au_id", suser_name(), getdate()) 11> IF UPDATE(au_lname) 12> INSERT UpdateLog VALUES ("authors", "au_lname", suser_name(), getdate()) 13> IF UPDATE(au_fname) 14> INSERT UpdateLog VALUES ("authors", "au_fname", suser_name(), getdate()) 15> IF UPDATE(phone) 16> INSERT UpdateLog VALUES ("authors", "phone", suser_name(), getdate()) 17> IF UPDATE(address) 18> INSERT UpdateLog VALUES ("authors", "address", suser_name(), getdate()) 19> IF UPDATE(city) 20> INSERT UpdateLog VALUES ("authors", "city", suser_name(), getdate()) 21> IF UPDATE(state) 22> INSERT UpdateLog VALUES ("authors", "state", suser_name(), getdate()) 23> IF UPDATE(zip) 24> INSERT UpdateLog VALUES ("authors", "zip", suser_name(), getdate()) 25> IF UPDATE(contract) 26> INSERT UpdateLog VALUES ("authors", "contract", suser_name(), getdate()) 27> END 28> GO 1> 2> 3> drop trigger trAuthors_Upd; 4> GO 1> 2> drop table sales; 3> drop table UpdateLog; 4> GO</source>