对于不可更新的视图要进行更新时,可以用触发器中的INSTEAD OF进行更新
-
-
CREATE TRIGGER STU_IN
-
ON SC
-
FOR INSERT
-
AS
-
UPDATE STUDENT
-
SET SCNUM = SCNUM +1
-
FROM STUDENT INNER JOIN SC
-
ON STUDENT.SNO = SC.SNO
-
-
set statistics io on
-
set nocount on
-
insert into sc values('990001','001','99')
-
delete from sc where sno='990001' and cno='001'
-
-
-
CREATE TRIGGER STU_UP
-
ON STUDENT
-
FOR UPDATE
-
AS
-
IF UPDATE(SNO)
-
BEGIN
-
RAISERROR('不能对SNO字段进行更新',10,1)
-
ROLLBACK TRANSACTION
-
END
-
-
UPDATE STUDENT
-
SET SNO='990032'
-
WHERE SNO='990001'
-
-
-
CREATE TRIGGER STU_DEL
-
ON STUDENT
-
FOR DELETE
-
AS
-
DELETE
-
FROM SC
-
WHERE SC.SNO IN(SELECT SNO FROM DELETED)
-
-
exec sp_helptext stu_in
-
-
CREATE TRIGGER COM_UP
-
ON COMPUTER
-
INSTEAD OF UPDATE
-
AS
-
IF UPDATE(SNAME)
-
BEGIN
-
UPDATE STUDENT
-
SET SNAME=INSERTED.SNAME
-
FROM STUDENT INNER JOIN INSERTED
-
ON STUDENT.SNO=INSERTED.SNO
-
END
-
ELSE
-
BEGIN
-
UPDATE SC
-
SET CNO=INSERTED.CNO,
- GRADE=INSERTED.GRADE
-
FROM SC INNER JOIN INSERTED
-
ON SC.SNO=INSERTED.SNO
-
END
-
-
UPDATE COMPUTER
-
SET GRADE=95
-
WHERE SNO='990028'
-
-
UPDATE COMPUTER
-
SET SNAME='王晓晓'
-
where SNO='990028'
-
-
SELECT * FROM COMPUTER
本文转自sucre03 51CTO博客,原文链接:http://blog.51cto.com/sucre/416796,如需转载请自行联系原作者