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

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

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

Call RAISERROR in a trigger

 

4>
5>
6>
7> CREATE TABLE Customers (
8>      CustomerID nchar (5) NOT NULL ,
9>      CompanyName nvarchar (40) NOT NULL ,
10>     ContactName nvarchar (30) NULL ,
11>     ContactTitle nvarchar (30) NULL ,
12>     Address nvarchar (60) NULL ,
13>     City nvarchar (15) NULL ,
14>     Region nvarchar (15) NULL ,
15>     PostalCode nvarchar (10) NULL ,
16>     Country nvarchar (15) NULL ,
17>     Phone nvarchar (24) NULL ,
18>     Fax nvarchar (24) NULL
19> )
20> 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>    CREATE VIEW CustomerOrders_vw
3>    WITH SCHEMABINDING
4>    AS
5>    SELECT   cu.rupanyName
6>    FROM     dbo.Customers  AS cu
7>    GO
1>
2>
3>
4>    CREATE TRIGGER trCustomerOrderUpdate ON CustomerOrders_vw
5>    INSTEAD OF UPDATE
6>    AS
7>    BEGIN
8>        IF (SELECT COUNT(*) FROM Inserted) > 0
9>        BEGIN
10>            RAISERROR("No matching Orders. Cannot perform insert",10,1)
11>        END
12>    END
13> GO
1> drop TRIGGER trCustomerOrderUpdate;
2> drop view CustomerOrders_vw;
3> drop table Customers;
4> GO



Define trigger for Insert and Update

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2> CREATE TRIGGER myTrigger
3>    ON Employee
4>    FOR INSERT, UPDATE
5> AS
6>    IF EXISTS
7>       (
8>        SELECT "True"
9>        FROM Inserted i
10>        JOIN employee e
11>          ON i.ID = e.ID
12>       )
13>    BEGIN
14>        RAISERROR("Transaction Failed.",16,1)
15>        ROLLBACK TRAN
16> END
17> GO
1>
2>
3> INSERT Employee (ID) VALUES (1)
4>
5>
6> drop table employee
7> GO
Msg 50000, Level 16, State 1, Server sqle\SQLEXPRESS, Procedure myTrigger, Line 14
Transaction Failed.
Msg 3609, Level 16, State 1, Server sqle\SQLEXPRESS, Line 3
The transaction ended in the trigger. The batch has been aborted.
1>



Trigger for FOR INSERT, UPDATE

 

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>
2> CREATE TABLE OrderDetails (
3>      OrderID int NOT NULL ,
4>      ProductID int NOT NULL ,
5>      UnitPrice money NOT NULL DEFAULT (0),
6>      Quantity smallint NOT NULL DEFAULT (1),
7>      Discount real NOT NULL DEFAULT (0)
8> )
9> GO
1>
2>    CREATE TRIGGER OrderDetailIsProduct
3>       ON OrderDetails
4>       FOR INSERT, UPDATE
5>    AS
6>       IF EXISTS
7>          (
8>           SELECT "True"
9>           FROM Inserted i
10>           LEFT JOIN Products p
11>              ON i.ProductID = p.ProductID
12>           WHERE p.ProductID IS NULL
13>          )
14>       BEGIN
15>          RAISERROR("Order Item Must Be a Valid Product" ,16,1)
16>          ROLLBACK TRAN
17>       END
18> GO
1> drop TRIGGER OrderDetailIsProduct;
2> GO
1> drop table OrderDetails;
2> GO
1> drop table Products;
2> GO



Trigger for update statement

1> create table employee(
2>     ID          int,
3>     name        nvarchar (10),
4>     salary      int,
5>     start_date  datetime,
6>     city        nvarchar (10),
7>     region      char (1))
8> GO
1>
2> insert into employee (ID, name,    salary, start_date, city,       region)
3>               values (1,  "Jason", 40420,  "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (2,  "Robert",14420,  "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (3,  "Celia", 24020,  "12/03/96", "Toronto",  "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (4,  "Linda", 40620,  "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (5,  "David", 80026,  "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (6,  "James", 70060,  "09/06/99", "Toronto",  "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (7,  "Alison",90620,  "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (8,  "Chris", 26020,  "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name,    salary, start_date, city,       region)
2>               values (9,  "Mary",  60020,  "06/09/02", "Toronto",  "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
(9 rows affected)
1>
2> CREATE TRIGGER myTrigger
3>    ON Employee
4>    FOR UPDATE
5> AS
6>    IF EXISTS
7>      (
8>       SELECT "True"
9>       FROM Inserted i
10>       JOIN Deleted d
11>         ON i.ID = d.ID
12>       WHERE d.salary - i.salary > 0 )
13>    BEGIN
14>      RAISERROR("Salary wrong.",16,1)
15>      ROLLBACK TRAN
16> END
17> GO
1>
2> update employee set salary = 0
3> GO
Msg 50000, Level 16, State 1, Server sqle\SQLEXPRESS, Procedure myTrigger, Line 14
Salary wrong.
Msg 3609, Level 16, State 1, Server sqle\SQLEXPRESS, Line 2
The transaction ended in the trigger. The batch has been aborted.
1>
2> drop table employee
3> GO
1>