请问这句代码有错误吗,有错误在那里????
CREATE TRIGGER [trg_Teacher] ON [dbo].[Teacher]
FOR INSERT, UPDATE, DELETE
AS
declare @TeacherID varchar(10)
select @TeacherID =TeacherID from inserted,deleted if update(@TeacherID) update from Class where TeacherID =@TeacherID
if delete(@TeacherID) delete from Class where TeacherID =@TeacherID
go
CREATE TRIGGER [trg_Teacher] ON [dbo].[Teacher]
FOR INSERT, UPDATE, DELETE
AS
declare @TeacherID varchar(10)
select @TeacherID =TeacherID from inserted,deleted if update(@TeacherID) update from Class where TeacherID =@TeacherID
if delete(@TeacherID) delete from Class where TeacherID =@TeacherID
go
CREATE TRIGGER [trg_Teacher] ON [dbo].[Teacher]
FOR INSERT, UPDATE, DELETE
AS
Declare @TeacherID varchar(10)
If Update(TeacherID) and Not Exists(Select 1 From Deleted)
Update A
From Class A Inner Join Inserted B
On A.TeacherID=B.TeacherID If Update(TeacherID) and Not Exists(Select 1 From Inserted )
Delete A
From Class A Inner Join Deleted B
On A.TeacherID=B.TeacherID
Go
CREATE TRIGGER [trg_Teacher] ON [dbo].[Teacher]
FOR INSERT, UPDATE, DELETE
AS
Declare @TeacherID varchar(10)
If Update(TeacherID) and Not Exists(Select 1 From Deleted)
BEGIN
Update A
Set A.TeacherID=B.TeacherID --刚才问题在这,忘写了
From Class A ,Inserted B,Deleted C
Where A.TeacherID=C.TeacherID
END
If Update(TeacherID) and Not Exists(Select 1 From Inserted )
BEGIN
Delete A
From Class A Inner Join Deleted B
On A.TeacherID=B.TeacherID
END
Go
不需要做触发器就可以达到你说的那效果了
那样要方便的多
--Teacher表
Create Table Teacher(TeacherID int)
Insert Teacher Select 1
Union all select 3
Union all select 4
Union all select 5
Union all select 6
--Class表
Create Table Class(TeacherID int)
Insert Class Select 1
Union all select 3
Union all select 4
Union all select 4
--触发器
CREATE TRIGGER Tri_Teacher ON Teacher
FOR UPDATE, DELETE
AS
Update A
Set A.TeacherID=B.TeacherID
From Class A ,Inserted B,Deleted C
Where A.TeacherID=C.TeacherID Delete A
From Class A Inner Join Deleted B
On A.TeacherID=B.TeacherID
----------------触发器创建结束--测试删除
Delete Teacher
Where teacherid=1
--测试更新
UPDATE class
Set teacherid=100
Where teacherid=4
--测试一下吧!
如果按照你的写法,那insert的时候,触发器里也要执行...from inserted,而这个inserted临时表根本不存在,那是会出错的。建立搂主分开写。
insert触发器时,inserted表中含新插入的行,deleted表为空.新插入的行同时被插入到基表中.
delete触发器时,inserted表为空,deleted表为被删除的行.被删除的行同时被从基表中删除.
update触发器时,inserted表和deleted表均不为空.inserted表中含更新后的行,deleted表中含更新前的行.基表中的对应行同时被更新.
楼主的触发器很值得商榷,因为TeacherID为主键或不是主键时处理方法是不一样的.