建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
SQLServer級(jí)聯(lián)更新的實(shí)現(xiàn)
SQL Server級(jí)聯(lián)更新如何實(shí)現(xiàn)，是很多人都提到過(guò)的問(wèn)題，下面就教您SQL Server級(jí)聯(lián)更新和級(jí)聯(lián)插入的方法，希望可以讓您對(duì)SQL Server級(jí)聯(lián)更新有更深的認(rèn)識(shí)。
--INSERT 數(shù)據(jù)插入，驗(yàn)證外鍵關(guān)聯(lián)邏輯，錯(cuò)誤拋出出錯(cuò)信息
- 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 數(shù)據(jù)更新，如果更新了外鍵，驗(yàn)證外鍵存在
- 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
文章名稱：SQLServer級(jí)聯(lián)更新的實(shí)現(xiàn)
標(biāo)題URL：http://fisionsoft.com.cn/article/djjppcj.html
