SQL Server级联更新如何实现,是很多人都提到过的问题,下面就教您SQL Server级联更新和级联插入的方法,希望可以让您对SQL Server级联更新有更深的认识。
--INSERT 数据插入,验证外键关联逻辑,错误抛出出错信息
- IF EXISTS (SELECT NAME FROM SYSOBJECTS
- WHERE NAME = 'INS_DOCUMENTTYPE_001' AND TYPE = 'TR')
- DROP TRIGGER INS_DOCUMENTTYPE_001
- GO
- CREATE TRIGGER INS_DOCUMENTTYPE_001
- ON dbo.DOCUMENTTYPE
- FOR INSERT
- AS
- IF (SELECT COUNT(*) FROM INSERTED WHERE UPNO IS NOT NULL
- AND (UPNO = NO OR UPNO NOT IN (SELECT NO FROM dbo.DOCUMENTTYPE))) > 0
- BEGIN
- DELETE FROM dbo.DOCUMENTTYPE WHERE NO IN (SELECT NO FROM INSERTED)
- RAISERROR('在被引用表 dbo.DOCUMENTTYPE 中没有与外键 UPNO 的引用列的列表匹配的主键或候选键。',16,1)
- END
- GO
--UPDATE 数据更新,如果更新了外键,验证外键存在
- IF EXISTS (SELECT NAME FROM SYSOBJECTS
- WHERE NAME = 'UPD_DOCUMENTTYPE_001' AND TYPE = 'TR')
- DROP TRIGGER UPD_DOCUMENTTYPE_001
- GO
- CREATE TRIGGER UPD_DOCUMENTTYPE_001
- ON dbo.DOCUMENTTYPE
- FOR UPDATE
- AS
- IF (SELECT COUNT(*) FROM INSERTED WHERE UPNO IS NOT NULL
- AND (UPNO = NO OR UPNO NOT IN (SELECT NO FROM dbo.DOCUMENTTYPE))) > 0
- BEGIN
- SET IDENTITY_INSERT dbo.DOCUMENTTYPE ON
- DELETE FROM dbo.DOCUMENTTYPE WHERE NO IN (SELECT NO FROM INSERTED)
- -- INSERT INTO dbo.DOCUMENTTYPE SELECT * FROM INSERTED
- SELECT * INTO dbo.DOCUMENTTYPE FROM INSERTED
- SET IDENTITY_INSERT dbo.DOCUMENTTYPE OFF
- RAISERROR('在被引用表 dbo.DOCUMENTTYPE 中没有与外键 UPNO 的引用列的列表匹配的主键或候选键。',16,1)
- END
- GO
【编辑推荐】