triggerdemo18.sql

T-SQL示例大全

/*
文件名称: TriggerDemo18.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
   UPDATE titles
   SET ytd_sales = ytd_sales + inserted.qty
   FROM inserted
   WHERE titles.title_id = inserted.title_id

GO

-- 新增单笔销售数据前，书号 PS2106 的销售总量
SELECT title_id,title,ytd_sales FROM titles WHERE title_id = 'PS2106'
GO

-- 试着新增数据记录
INSERT sales
(
stor_id,ord_num,ord_date,qty,payterms,title_id
)
VALUES
(
'7896','X0001','06/16/2001',2000,'ON invoice','PS2106'
)

GO

-- 新增单笔销售数据后，书号 PS2106 的销售总量
SELECT title_id,title,ytd_sales FROM titles WHERE title_id = 'PS2106'
GO