日期:2014-05-17 浏览次数:20674 次
IF OBJECT_ID('T_CSDN_ONE') IS NOT NULL
BEGIN
DROP TABLE dbo.T_CSDN_ONE;
END
GO
CREATE TABLE T_CSDN_ONE
(
ID INT IDENTITY(1,1) PRIMARY KEY,
[NAME] VARCHAR(50),
NUMBER INT
)
INSERT INTO dbo.T_CSDN_ONE
SELECT 'A',20
UNION ALL
SELECT 'B',20
UNION ALL
SELECT 'A',10
UNION ALL
SELECT 'A',5
UNION ALL
SELECT 'B',40
UNION ALL
SELECT 'B',10
SELECT * FROM dbo.T_CSDN_ONE
IF OBJECT_ID('T_CSDN_TWO') IS NOT NULL
BEGIN
DROP TABLE dbo.T_CSDN_TWO;
END
GO
CREATE TABLE T_CSDN_TWO
(
[NAME] VARCHAR(50),
[SUMNUMBER] INT
)
INSERT INTO T_CSDN_TWO
SELECT 'A',35
UNION ALL
SELECT 'B',70
GO
CREATE TRIGGER TR_DELETE
ON T_CSDN_ONE
AFTER DELETE
AS
BEGIN
UPDATE T_CSDN_TWO SET SUMNUMBER=SUMNUMBER-(SELECT ISNULL(SUM(NUMBER),0) FROM DELETED WHERE NAME=T_CSDN_TWO.NAME)
END
DELETE T_CSDN_ONE WHERE ID IN (4,3,5)
SELECT * FROM dbo.T_CSDN_ONE
SELECT * FROM dbo.T_CSDN_TWO