triggerdemo19.sql

T-SQL示例大全

/*
文件名称: TriggerDemo19.sql
*/
USE pubs
GO

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.InsertOfSales') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger dbo.InsertOfSales
GO

-- 建立一个由新增作业所引发的 AFTER 触发程序
-- 单一数据记录或多个数据记录新增都是正确的
CREATE TRIGGER InsertOfSales
ON sales
AFTER INSERT
AS
UPDATE titles
SET ytd_sales = ytd_sales +
     (SELECT SUM(qty) FROM inserted WHERE titles.title_id = inserted.title_id)
WHERE titles.title_id IN
     (SELECT title_id FROM inserted)

GO

-- 声明一个 table 变量以便用来存放要批量新增的多个销售数据
DECLARE @sales TABLE
(
stor_id char(4) NOT NULL ,
ord_num varchar(20) NULL ,
ord_date datetime NOT NULL ,
qty smallint NOT NULL ,
payterms varchar(12) NOT NULL ,
title_id varchar(6) NOT NULL 
)

-- 将书号 TC7777 的销售数据新增至 table 变量 @sales 中
INSERT @sales
(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES
('8042','A0001','06/16/2002',1000,'Net 60','TC7777')

INSERT @sales
(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES
('8042','A0002','06/16/2002',1000,'Net 60','TC7777')

INSERT @sales
(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES
('8042','A0003','06/16/2002',1000,'Net 60','TC7777')

-- 将书号 TC4203 的销售数据新增至 table 变量 @sales 中
INSERT @sales
(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES
('7067','B0001','06/16/2002',2000,'Net 30','TC4203')

INSERT @sales
(stor_id,ord_num,ord_date,qty,payterms,title_id)
VALUES
('7067','B0002','06/16/2002',1500,'Net 30','TC4203')

-- 批量新增销售数据前，书号 TC7777 与 TC4203 的销售总量
SELECT title_id,title,ytd_sales FROM titles WHERE title_id IN ('TC7777','TC4203')

-- 将 table 变量 @sales 中的多个销售记录批量新增至 sales 数据表
INSERT sales
    SELECT * FROM @sales

-- 批量新增销售数据后，书号 TC7777 与 TC4203 的销售总量
SELECT title_id,title,ytd_sales FROM titles WHERE title_id IN ('TC7777','TC4203')