triggerdemo20.sql

来自「T-SQL示例大全」· SQL 代码 · 共 93 行

/*
文件名称: TriggerDemo20.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

-- 单个数据记录或多个数据记录新增都是正确的
-- 并且会自行判断，以便在单个新增时也能有最佳的效率
CREATE TRIGGER InsertOfSales
ON sales
AFTER INSERT
AS
IF @@ROWCOUNT = 1
BEGIN
   UPDATE titles
   SET ytd_sales = ytd_sales + qty
   FROM inserted
   WHERE titles.title_id = inserted.title_id
END
ELSE
BEGIN
   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)
END
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')

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

-- 新增单个销售数据
INSERT sales
(
stor_id,ord_num,ord_date,qty,payterms,title_id
)
VALUES
(
'7896','X0001','06/16/2001',2000,'ON invoice','PS2106'
)

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

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