CREATE TRIGGER S90Login_Version_Control ON [dbo].[role_permission] 
FOR INSERT,UPDATE
AS
declare @program_no as varchar(100)
SELECT  @program_no = convert(decimal,RIGHT(rtrim(isnull(program_no,'0')),2))  from [dbo].[program_authority]  where  system_no='S9Z' 
if @program_no < 31 
BEGIN 
       update  [dbo].[role_permission] set deny_all_cmd='Y'
END



CREATE TRIGGER [Insert_Prod_location] ON [dbo].[SAP_location] 
AFTER INSERT AS
declare @code as varchar(1)
declare @trans_date as varchar(8)
declare @real_qty as numeric

SELECT  @code = code, @trans_date = trans_date, @real_qty = last_stock - last_sale + last_rtn FROM inserted 

IF @trans_date = convert(varchar(8),getdate(),112)
BEGIN

if exists
(select * from Prod_location, inserted
WHERE Prod_location.prod_no = inserted.prod_no
AND Prod_location.location = inserted.location )
BEGIN
	IF @code = 'B'
	BEGIN			
		UPDATE Prod_location 
			SET Prod_location.last_stock = inserted.last_stock, 
				Prod_location.last_sale = inserted.last_sale, 
				Prod_location.last_rtn = inserted.last_rtn, 
				Prod_location.real_qty = @real_qty
			FROM Prod_location ,inserted
				WHERE Prod_location.prod_no = inserted.prod_no
				AND Prod_location.location = inserted.location 
	END
	ELSE
	BEGIN
		UPDATE Prod_location 
			SET Prod_location.trans_qty = Prod_location.trans_qty +  inserted.trans_qty, 
				Prod_location.real_qty = (@real_qty + Prod_location.trans_qty +  inserted.trans_qty)
			FROM Prod_location ,inserted
				WHERE Prod_location.prod_no = inserted.prod_no
				AND Prod_location.location = inserted.location 
	END
END
ELSE
BEGIN
	IF @code = 'B'
	BEGIN			
		INSERT Prod_location 
			SELECT dept_no, plant, place, location, prod_no, last_stock, last_sale, last_rtn, 0, NULL, NULL, 
				(last_stock - last_sale + last_rtn), NULL
			FROM inserted 
	END
END

END
ELSE
BEGIN
	if exists
	(select * from Prod_location, inserted
	WHERE Prod_location.prod_no = inserted.prod_no
	AND Prod_location.location = inserted.location )
	BEGIN
		IF @code = 'B' AND @trans_date = CONVERT(varchar(8),DATEADD(day, - 1, GETDATE()), 112)
		BEGIN	
			UPDATE Prod_location 
			SET Prod_location.last_stock = inserted.last_stock, 
				Prod_location.last_sale = inserted.last_sale, 
				Prod_location.last_rtn = inserted.last_rtn, 
				Prod_location.real_qty = @real_qty,
				Prod_location.trans_qty = @real_qty + Prod_location.real_qty 
			FROM Prod_location ,inserted
				WHERE Prod_location.prod_no = inserted.prod_no
				AND Prod_location.location = inserted.location 
				
		END
	END
END

A. 儲存單一資料列插入的累加值
CREATE TRIGGER NewPODetail
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID ;

B. 儲存多資料列或單一資料列插入的累加值
CREATE TRIGGER NewPODetail2
ON Purchasing.PurchaseOrderDetail
AFTER INSERT AS
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted);

C. 根據插入類型儲存累加值
您可以變更觸發程序，使用最適於多個資料列的方法。例如，@@ROWCOUNT 函數可以在觸發程序邏輯中使用，以區別單一資料列插入及多資料列插入。
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
BEGIN
   UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID

END
ELSE
BEGIN
      UPDATE PurchaseOrderHeader
   SET SubTotal = SubTotal + 
      (SELECT SUM(LineTotal)
      FROM inserted
      WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
   WHERE PurchaseOrderHeader.PurchaseOrderID IN
      (SELECT PurchaseOrderID FROM inserted)
END;


下列範例使用 @@FETCH_STATUS 控制 WHILE 迴圈中的資料指標活動。

複製
DECLARE Employee_Cursor CURSOR FOR
SELECT EmployeeID, Title 
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID =3;
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor;
WHILE @@FETCH_STATUS = 0
   BEGIN
      FETCH NEXT FROM Employee_Cursor;
   END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO


下列範例Using IF UPDATE() in a Trigger

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

 CREATE TRIGGER tru_Employee ON Employee FOR UPDATE
4> AS
5> IF @@ROWCOUNT = 0
6>   RETURN
7> IF UPDATE (first_name) OR UPDATE (last_name)
8> BEGIN
9>   IF EXISTS (SELECT * FROM inserted WHERE salary < 0)
10>   BEGIN
11>     Print 'logic'
12>   END
13> END
14> GO


下列範例Use COLUMNS_UPDATED

CREATE TRIGGER updEmployeeData 
ON employeeData 
FOR update AS
/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/

   IF (COLUMNS_UPDATED() & 14) > 0
/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/
      BEGIN
-- Audit OLD record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'OLD', 
            del.emp_id,
            del.emp_bankAccountNumber,
            del.emp_salary,
            del.emp_SSN
         FROM deleted del

-- Audit NEW record.
      INSERT INTO auditEmployeeData
         (audit_log_type,
         audit_emp_id,
         audit_emp_bankAccountNumber,
         audit_emp_salary,
         audit_emp_SSN)
         SELECT 'NEW',
            ins.emp_id,
            ins.emp_bankAccountNumber,
            ins.emp_salary,
            ins.emp_SSN
         FROM inserted ins
   END
GO


下列範例 Use COLUMNS_UPDATED to test more than 8 columns

If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE Northwind
DROP TRIGGER  tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
   IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
      + power(2,(5-1))) 
      AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
      ) 
   PRINT 'Columns 3, 5 and 9 updated'
GO


/*下列範例*********************************/
/*回复：代?20080516002 ?：00000000015    */
/*主?：通?触?器自???原表非重复?据     */
/*作者：二等草                             */
/******************************************/
set nocount on
/************例子?据 begin****************/
create table ta(a int,b int,c int)--a，b?主?
create table tb(b int,c int) --??ta表存在的b，c不同的值
go
/************例子?据 end******************/

/************代?     begin***************/
create trigger t_ab on ta for insert,update
as
begin
 delete a from tb a where exists(select 1 from deleted b where a.b = b and a.c = c 
                          and  not exists(select 1 from ta where b.b=b and b.c = c))
 insert tb select distinct b,c from inserted a where not exists(select 1 from tb where a.b=b and a.c = c)
end
go
--??
insert ta select 1,2,3 union select 2,2,3 union select 3,2,3 union select 1,3,2
select distinct 'a', b,c from ta
select 'b',* from tb
update ta set c = 6 where a =1 or  a = 2
select distinct 'a',b,c from ta
select 'b',* from tb
go
/************代?     end*****************/

/************?果     begin***************
    b           c           
---- ----------- ----------- 
a    2           3
a    3           2

     b           c           
---- ----------- ----------- 
b    2           3
b    3           2

     b           c           
---- ----------- ----------- 
a    2           3
a    2           6
a    3           6

     b           c           
---- ----------- ----------- 
b    2           3
b    2           6
b    3           6

 ************?果     end*****************/

/************清除*************************/
drop table ta,tb