急!sql 触发器 代码

发布网友 发布时间：2022-05-05 04:38

我来回答

共3个回答

热心网友 时间：2022-05-05 06:07

use tempdb
go
create table employee
(
emp_id int primary key ,
emp_name varchar(20) not null,
dept_id int not null,
)
create table department
(
dept_id int primary key,
dept_name varchar(20) not null,
)

insert into employee values(1,'zyy',1)
insert into employee values(2,'lzl',2)
insert into employee values(3,'gui',1)
insert into employee values(4,'ding',4)

update employee set dept_id=1 where emp_id=2

delete from department where dept_id=1

insert into department values(1,'软件部')
insert into department values(2,'人事部')
insert into department values(3,'软件部')

create trigger trigger_look
on employee instead of insert as
begin
if exists(select * from department where dept_id in (select dept_id from inserted))
begin
insert into employee select * from inserted
end
else
begin
print '部门不存在！'
end
end

create trigger trigger_del
on department instead of delete as
begin
if exists(select * from department where dept_id in (select dept_id from deleted))
begin
delete from department where dept_id = (select dept_id from deleted)
delete from employee where dept_id =(select dept_id from deleted)
end
else
begin
print '部门不存在！'
end
end

select * from department
select * from employee

drop table employee
drop table department

/*
注: if update 不但跟update动作相关,同时if update 也跟insert动作相关，
所以并不能通过if update 来判断是哪种动作，等一下通过netmeeting讲讲
*/

热心网友 时间：2022-05-05 07:25

a b 两表建立外键关系，a为主表b为外键表，关系是两表的id列
注意勾选 “级联删除”

热心网友 时间：2022-05-05 09:00

使用约束也可以实现你得功能，使用触发器你可参考SqlServer得帮助(你得是SqlServer吗)