SQL Server/T-SQL Tutorial/Trigger/Trigger
Версия от 13:46, 26 мая 2010; (обсуждение)
Содержание
- 1 Check business logic in a trigger
- 2 Check record matching in a trigger
- 3 Check @@ROWCOUNT in a trigger
- 4 Define variables in a trigger
- 5 Disable a trigger
- 6 Enable a trigger
- 7 exits if the price column has not been updated.
- 8 INSTEAD OF INSERT trigger for a table
- 9 RAISERROR in trigger
- 10 Rollback transaction in a trigger
- 11 Table for INSTEAD OF Trigger for Logical Deletes
- 12 The syntax of the CREATE TRIGGER statement
- 13 Trigger Scripts for Cascading DELETEs
- 14 Update table in a trigger
Check business logic in a trigger
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>
Check record matching in a trigger
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
Check @@ROWCOUNT in a trigger
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>
Define variables in a trigger
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>
Disable a 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>
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
Enable a trigger
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
exits if the price column has not been updated.
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>
INSTEAD OF INSERT trigger for a table
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>
RAISERROR in trigger
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
Rollback transaction in a trigger
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
Table for INSTEAD OF Trigger for Logical Deletes
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
The syntax of the CREATE TRIGGER statement
CREATE TRIGGER trigger_name
ON {table_name|view_name}
[WITH ENCRYPTION]
{FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE]
AS sql_statements
Trigger Scripts for Cascading DELETEs
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.
Update table in a trigger
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