CREATE TRIGGER GoodOrder_Before_InsTrg BEFORE INSERT
Order 1 ON GoodOrder
REFERENCING NEW AS New_GoodOrder
FOR EACH ROW
BEGIN
--/更新入库单号
IF New_GoodOrder.Tag = 'N'THEN
UPDate sys_auto_Order
SET Order_number = New_GoodOrder.GoodOrder
WHERE sys_auto_Order.Order_pre ='GoodOrder'
END IF
END;
REFERENCING NEW AS 这个T-SQL帮助里面也有,为什么不支持呢?
如何修改?
Order 1 ON GoodOrder
REFERENCING NEW AS New_GoodOrder
FOR EACH ROW
BEGIN
--/更新入库单号
IF New_GoodOrder.Tag = 'N'THEN
UPDate sys_auto_Order
SET Order_number = New_GoodOrder.GoodOrder
WHERE sys_auto_Order.Order_pre ='GoodOrder'
END IF
END;
REFERENCING NEW AS 这个T-SQL帮助里面也有,为什么不支持呢?
如何修改?
ON GoodOrder
AFTER INSERT
AS
BEGIN
if exists(select * from inserted where Tag='N')
update sys_auto_Order
set Order_number=inserted.GoodOrder
where sys_auto_Order.Order_pre ='GoodOrder';
END
GO
ON GoodOrder
AFTER INSERT
AS
BEGIN
if exists(select * from inserted where Tag='N')
update sys_auto_Order
set Order_number=inserted.GoodOrder
where sys_auto_Order.Order_pre ='GoodOrder'; END
Order 1 ON GoodOrder
REFERENCING NEW AS New_GoodOrder
FOR EACH ROW
BEGIN
--/更新入库单号
IF New_GoodOrder.Tag = 'N'THEN
UPDate sys_auto_Order
SET Order_number = New_GoodOrder.GoodOrder
WHERE sys_auto_Order.Order_pre ='GoodOrder'
END IF
END;
REFERENCING NEW AS 这个T-SQL帮助里面也有,为什么不支持呢?
如何修改? 事例示例
A. 使用包含提醒消息的 DML 触发器
如果有人试图在 Customer 表中添加或更改数据,下列 DML 触发器将向客户端显示一条消息。 复制代码
USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
DROP TRIGGER Sales.reminder1;
GO
CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO B. 使用包含提醒电子邮件的 DML 触发器
如果 Customer 表发生更改,以下示例将向指定人员 (MaryM) 发送电子邮件。 复制代码
USE AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = '[email protected]',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO C. 使用 DML AFTER 触发器在 PurchaseOrderHeader 和 Vendor 表之间强制实现业务规则
由于 CHECK 约束只能引用定义了列级或表级约束的列,表间的任何约束(在本例中是业务规则)都必须定义为触发器。以下示例将创建一个 DML 触发器。如果有人试图将一个新采购订单插入到 PurchaseOrderHeader 表中,此触发器将进行检查以确保供应商具有良好的信用等级。若要获取供应商的信用等级,必须引用 Vendor 表。如果信用等级太低,则显示信息,并且不执行该插入操作。注意:
若要查看可更新多个行的 DML AFTER 触发器示例,请参阅 DML 触发器的多行注意事项。若要查看 DML INSTEAD OF INSERT 触发器的示例,请参阅 INSTEAD OF INSERT 触发器。
复制代码
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
DROP TRIGGER Purchasing.LowCredit;
GO
CREATE TRIGGER LowCredit ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
DECLARE @creditrating tinyint,
@vendorid int
SELECT @creditrating = v.CreditRating, @vendorid = p.VendorID
FROM Purchasing.PurchaseOrderHeader AS p
INNER JOIN inserted AS i ON p.PurchaseOrderID =
i.PurchaseOrderID
JOIN Purchasing.Vendor AS v on v.VendorID = i.VendorID
IF @creditrating = 5
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1)
ROLLBACK TRANSACTION
END
GO D. 使用延迟名称解析
以下示例将创建两个触发器,用于阐释延迟名称解析。 复制代码
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS
SELECT e.EmployeeID, e.BirthDate, x.info
FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x
ON e.EmployeeID = x.xID
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO-- Creating a trigger on an existing table, but with a nonexistent
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
DROP TRIGGER HumanResources.trig2
GO
CREATE TRIGGER HumanResources.trig2
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS
DECLARE @fax varchar(12)
SELECT @fax = 'AltPhone'
FROM HumanResources.Employee
GO
-- Here is the statement to actually see the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO
E. 运用具有数据库范围的 DDL 触发器
下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。 复制代码
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.triggers
WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety
ON DATABASE
FOR DROP_SYNONYM
AS
RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
ROLLBACK
GO
DROP TRIGGER safety
ON DATABASE;
GO
F. 运用具有服务器范围的 DDL 触发器
在以下示例中,如果当前服务器实例上出现任何 CREATE DATABASE 事件,则使用 DDL 触发器输出一条消息,并使用 EVENTDATA 函数检索对应 Transact-SQL 语句的文本。注意:
若要查看在 DDL 触发器中使用 EVENTDATA 的更多示例,请参阅使用 EVENTDATA 函数。
复制代码
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
PRINT 'Database Created.'
SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO