create trigger tri on tb for update as begin if @@rowcount>1 begin raiserror ('一次只能更新一条记录',16,1) rollback tran return end declare @new_name varchar(20) declare @old_name varchar(20) select @new_name=name from inserted select @old_name=name from deleted
update tb2 set name=@new_name where name=@old_name update tb3 set name=@new_name where name=@old_name end go
象你描述的这种情况完全可以不要见储发器,完全可以通过指定表内的主键和指定表间的外键并设置级联更新和级联删除关系来控制。如你把表设计成以下结构和关系:CREATE TABLE [学生] ( [学号] [char] (10) NOT NULL , [姓名] [char] (10) NULL , CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED ( [学号] ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [成绩] ( [学号] [char] (10) NOT NULL , [课程编号] [char] (10) NOT NULL , [分数] [int] NULL , CONSTRAINT [PK_成绩] PRIMARY KEY CLUSTERED ( [学号], [课程编号] ) ON [PRIMARY] , CONSTRAINT [FK_成绩_学生] FOREIGN KEY ( [学号] ) REFERENCES [学生] ( [学号] ) ON DELETE CASCADE ON UPDATE CASCADE ) ON [PRIMARY] GO这样当你更改学生表中的学号时成绩表中的学号也会一并变化。
学生 课程 成绩 三个表 都有学生号和姓名改学生时更新另外两个表的学生姓名时,需要建触发器,如果改学生号更新另外两个表的学生号时用级连更新处理.. --建个触发器 create trigger 学生_update on 学生 for update as if update(姓名)--更新姓名时才触发此触发器 begin update 课程 set 姓名=i.姓名 from inserted i where i.学生号=课程.学生号 update 成绩 set 姓名=i.姓名 from inserted i where i.学生号=成绩.学生号 end
for update
as
begin
if @@rowcount>1
begin
raiserror ('一次只能更新一条记录',16,1)
rollback tran
return
end
declare @new_name varchar(20)
declare @old_name varchar(20)
select @new_name=name from inserted
select @old_name=name from deleted
update tb2 set name=@new_name where name=@old_name
update tb3 set name=@new_name where name=@old_name
end
go
[学号] [char] (10) NOT NULL ,
[姓名] [char] (10) NULL ,
CONSTRAINT [PK_学生] PRIMARY KEY CLUSTERED
(
[学号]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [成绩] (
[学号] [char] (10) NOT NULL ,
[课程编号] [char] (10) NOT NULL ,
[分数] [int] NULL ,
CONSTRAINT [PK_成绩] PRIMARY KEY CLUSTERED
(
[学号],
[课程编号]
) ON [PRIMARY] ,
CONSTRAINT [FK_成绩_学生] FOREIGN KEY
(
[学号]
) REFERENCES [学生] (
[学号]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO这样当你更改学生表中的学号时成绩表中的学号也会一并变化。
--建个触发器
create trigger 学生_update on 学生
for update
as
if update(姓名)--更新姓名时才触发此触发器
begin
update 课程 set 姓名=i.姓名 from inserted i where i.学生号=课程.学生号
update 成绩 set 姓名=i.姓名 from inserted i where i.学生号=成绩.学生号
end