create trigger trg_insert on jobs for insert as declare @job_id smallint select @job_id=a.job_id from jobs a inner join inserted b on a.job_id=b.job_id if @job_id=15 begin RAISERROR('不能新增',16,8)--触发器提示信息 rollback tran--回滚触发器 end --测试语句 insert into jobs values('test',11,11)
CREATE TRIGGER XX_TG ON A FOR INSERT AS BEGININSERT INTO B SELECT * FROM inserted END
create table tableA ( e_id int identity(1,1) primary key, e_name varchar(20) , e_sex varchar(2), e_age varchar(10) )create table tableB ( e_id int identity(1,1) primary key, e_name varchar(20) , e_sex varchar(2), e_age varchar(10) )create trigger tr_A on tableA for insert as begin insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted end select *from tableA select *from tableB delete tableAinsert into tableA(e_name,e_sex,e_age) values('zhangsan','1',50) insert into tableA(e_name,e_sex,e_age) values('李四','0',18) 借鉴了上面高人的,功能可以实现了. 小弟还想补充点 就是对tableA的所有操作对tableB有同样的影响 操作包括,增加,删除,修改 增加上面已经给出来了...谢谢回贴.
/* 对tableA操作时,同时影响到tableB insert delete update */ create table tableA ( e_id int identity(1,1) primary key, e_name varchar(20) , e_sex varchar(2), e_age varchar(10) )create table tableB ( e_id int identity(1,1) primary key, e_name varchar(20) , e_sex varchar(2), e_age varchar(10) )drop trigger tr_A_Insert create trigger tr_A_Insert on tableA for insert as begin insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted end drop trigger tr_A_Update create trigger tr_A_Update on tableA for update as begin declare @iseq varchar(20) select @iseq=d.e_name from deleted d delete from tableB where e_name=@iseq insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted end--删除触发器一: drop trigger tr_A_Delete create trigger tr_A_Delete on tableA for delete as begin delete tableB from deleted tableA inner join tableB on tableB.e_name=tableA.e_name end--删除触发器二: create trigger tr_A_Delete_B on tableA after delete as begin declare @iseq varchar(20) set nocount on select @iseq=d.e_name from deleted d delete from table_b where e_name=@iseq end --select *from tableA --select *from tableB --update tableA set e_name='zhangzhang123' where e_id=7--delete tableA --delete tableB--insert into tableA(e_name,e_sex,e_age) values('zhangsan','1',50) --insert into tableA(e_name,e_sex,e_age) values('李四','0',18) 搞定了 修改A表的数据后,同时删除B表的数据,再重新插入A表修改后的数据 这种方法虽然也可以实现功能,应该还有更好的办法...好的方法欢迎拿出来共享.... 揭贴。
on jobs for insert
as
declare @job_id smallint
select @job_id=a.job_id from jobs a inner join inserted b on a.job_id=b.job_id
if @job_id=15
begin
RAISERROR('不能新增',16,8)--触发器提示信息
rollback tran--回滚触发器
end
--测试语句
insert into jobs values('test',11,11)
CREATE TRIGGER XX_TG ON A
FOR INSERT
AS
BEGININSERT INTO B SELECT * FROM inserted END
(
e_id int identity(1,1) primary key,
e_name varchar(20) ,
e_sex varchar(2),
e_age varchar(10)
)create table tableB
(
e_id int identity(1,1) primary key,
e_name varchar(20) ,
e_sex varchar(2),
e_age varchar(10)
)create trigger tr_A
on tableA
for insert
as
begin
insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted
end
select *from tableA
select *from tableB
delete tableAinsert into tableA(e_name,e_sex,e_age) values('zhangsan','1',50)
insert into tableA(e_name,e_sex,e_age) values('李四','0',18)
借鉴了上面高人的,功能可以实现了.
小弟还想补充点
就是对tableA的所有操作对tableB有同样的影响
操作包括,增加,删除,修改
增加上面已经给出来了...谢谢回贴.
对tableA操作时,同时影响到tableB
insert delete update
*/
create table tableA
(
e_id int identity(1,1) primary key,
e_name varchar(20) ,
e_sex varchar(2),
e_age varchar(10)
)create table tableB
(
e_id int identity(1,1) primary key,
e_name varchar(20) ,
e_sex varchar(2),
e_age varchar(10)
)drop trigger tr_A_Insert
create trigger tr_A_Insert
on tableA
for insert
as
begin
insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted
end
drop trigger tr_A_Update
create trigger tr_A_Update
on tableA
for update
as
begin
declare @iseq varchar(20)
select @iseq=d.e_name from deleted d
delete from tableB where e_name=@iseq
insert into tableB(e_name,e_sex,e_age) select e_name,e_sex,e_age from inserted
end--删除触发器一:
drop trigger tr_A_Delete
create trigger tr_A_Delete
on tableA
for delete
as
begin
delete tableB from deleted tableA inner join tableB on tableB.e_name=tableA.e_name
end--删除触发器二:
create trigger tr_A_Delete_B
on tableA
after delete
as
begin
declare @iseq varchar(20)
set nocount on
select @iseq=d.e_name from deleted d
delete from table_b where e_name=@iseq
end
--select *from tableA
--select *from tableB
--update tableA set e_name='zhangzhang123' where e_id=7--delete tableA
--delete tableB--insert into tableA(e_name,e_sex,e_age) values('zhangsan','1',50)
--insert into tableA(e_name,e_sex,e_age) values('李四','0',18)
搞定了
修改A表的数据后,同时删除B表的数据,再重新插入A表修改后的数据
这种方法虽然也可以实现功能,应该还有更好的办法...好的方法欢迎拿出来共享....
揭贴。