我想当text表一条数据有更新操作时,把原数据插入test2表中
create trigger test3
on test for update
as
declare @usern char(20),@sex3 char(20)
select @usern=inserted.names from inserted
select @sex3=inserted.sex from inserted
begin
if @usern is not null
insert into test2(names,sex)
values(@usern,@sex3)
end我的这个test2中插入的也是更新后的数据,请问@usern和@sex3怎么赋值才能实现我要的效果,谢谢!
create trigger test3
on test for update
as
declare @usern char(20),@sex3 char(20)
select @usern=inserted.names from inserted
select @sex3=inserted.sex from inserted
begin
if @usern is not null
insert into test2(names,sex)
values(@usern,@sex3)
end我的这个test2中插入的也是更新后的数据,请问@usern和@sex3怎么赋值才能实现我要的效果,谢谢!
解决方案 »
- 合并表中数据
- 取表1的userName的值后,执行一系列的SQL语句,表1中的每条记录作为这些语句其中一个值,每条记录分别执行一次.
- 怎样查看磁带剩余的空间
- 求助~~~ Sql语句怎么写,考虑效率!!(急,在线等)
- sql 2008 附加数据乱码问题
- 设置自动断开连接的问题
- 返回指定列的索引名称
- SSIS参数传递
- 更新语句无相应,为何?updateblob bylx set bywj = :lb_bywj where bybh = :li_bybh using sqlca;
- 紧急!存储过程中如何调用系统月份来求取与之对应的字段数据和?
- 存储过程重直接使用out参数和直接select哪个效率高?比如获取自增的列值
- 请问这样的汇总怎么写?
on test for update
as
--插入更新后的数据
insert into test2(names,sex)
select names,sex from inserted
on test for update
as
--插入原数据
insert into test2(names,sex)
select names,sex from deleted
on test for update
as
--插入更新前的数据
insert into test2(names,sex)
select names,sex from deleted
go
on test for update
as
insert into test2(names,sex) select names,sex from deleted
end
for update
as
insert into test2(names,sex)
select names,sex from inserted where names is not null
for update
as
--要插入test的原数据,应该用deleted表。
insert into test2(names,sex)
select names,sex from deleted
create trigger test3
on test for update
as
declare @usern char(20),@sex3 char(20)
select @usern=inserted.names from deleted
select @sex3=inserted.sex from deleted
begin
if @usern is not null
insert into test2(names,sex)
values(@usern,@sex3)
end
错误是这样:
服务器: 消息 107,级别 16,状态 2,过程 test3,行 5
列前缀 'inserted' 与查询中所用的表名或别名不匹配。
服务器: 消息 107,级别 16,状态 1,过程 test3,行 6
列前缀 'inserted' 与查询中所用的表名或别名不匹配。
create trigger test3 on test
for update
as
--要插入test的原数据,应该用deleted表。
--还要判断更新前的names不为NULL
insert into test2(names,sex)
select names,sex from deleted where names is not null
create trigger test3 on test
for update
as
insert into test2(names,sex)
select names,sex from inserted where names is not null