SQL Server/T-SQL/Trigger/Trigger for update

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

Call RAISERROR in a trigger

   <source lang="sql">

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

</source>
   
  


Define trigger for Insert and Update

   <source lang="sql">

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>

      </source>
   
  


Trigger for FOR INSERT, UPDATE

   <source lang="sql">

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

</source>
   
  


Trigger for update statement

   <source lang="sql">

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>

      </source>