SQL Server/T-SQL Tutorial/Trigger/Trigger for after — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 10:24, 26 мая 2010
Содержание
An after-update trigger
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
One table with two after-delete triggers
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>
Rolling Back in an AFTER Trigger
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
Using an AFTER Trigger to Remove Time from a datetime Column
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