楼主要做什么?
update触发器,楼主写语句是insert
update触发器,楼主写语句是insert
解决方案 »
- 如何查询逗号隔开的列
- 求一个SQL语句,大虾进来,给分
- begin/rollback transaction的问题?
- 打开SQL2005在登陆窗口出现前 弹出个感叹号窗口.上面什么说明也没有.空的.点了之后一切正常.
- WIN 7系统安装SQL2000是出现demoshield run-time player如何处理?
- 关于触发器,如何获取插入过程后产生的数据
- 第一次接触SQL,请问如何实现在客户端通过URL访问数据库?
- 如何仅备份库中表的数据
- 这个查询怎么写才对?
- 请教mssql server 存储过程参数的 OUT 和 OUTPUT 选项的区别。
- EXEC执行动态SQL时设置变量的问题
- 多表查询的问题,请各位帮帮我。
(id int identity(1,1)primary key,
num int ) alter table aa drop column id?因为列有了主键约束,把约束先删除就可以了
(id int identity(1,1)primary key,
num int ) exec sp_helpconstraint aa --查看主键名称alter table aa drop constraint PK__aa__695C9DA1alter table aa drop column id --成功select * from aa
这个问题这磨难,高手难倒一大片?
create table bb
(id int identity(1,1),--primary key
num int )
create table aa
(id int identity(1,1) primary key,
num int )
insert into aa values(1)
insert into aa values(2)
insert into aa values(3)
select * from aa
insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
select * from bbcreate trigger tr
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end --修改bb表num列的值,报错!drop trigger tr
drop table aa,bb
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end --------------------
你这个触发器中是通过id去关联的,把id列删掉了。这个触发器中还在用id列。
且会不报错?
(id int identity(1,1)primary key,
num int ) alter table aa drop column id? 因为列有了主键约束,把约束先删除就可以了create table aa
(id int identity(1,1)primary key,
num int ) exec sp_helpconstraint aa --查看主键名称alter table aa drop constraint PK__aa__695C9DA1alter table aa drop column id --成功select * from aa
(id int identity(1,1), --设置主键一切正常,不设置报错! num int )
create table aa
(id int identity(1,1),
num int )
insert into aa values(1)
insert into aa values(2)
insert into aa values(3)
select * from aa
insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
select * from bb create trigger tr
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end --若修改bb表num列的值,报错! drop trigger tr
drop table aa,bb
create table bb(id int identity(1,1), num int )
create table aa(id int identity(1,1), num int )
insert into aa values(1)
insert into aa values(2)
insert into aa values(3)
--select * from aa
insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
--select * from bb go
create trigger tr
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end
goselect * from bb
/*
id num
----------- -----------
1 1
2 2
3 3(3 row(s) affected)
*/
select * from aa
/*
id num
----------- -----------
1 1
2 2
3 3(3 row(s) affected)
*/update bb set num=num+1select * from bb
/*
id num
----------- -----------
1 2
2 3
3 4(3 row(s) affected)
*/
select * from aa
/*
id num
----------- -----------
1 3
2 5
3 7(3 row(s) affected)
*/drop trigger tr
drop table aa,bb
(id int identity(1,1),
num int )
create table aa
(id int identity(1,1),
num int )
insert into aa values(1)
insert into aa values(2)
insert into aa values(3)
select * from aa
insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
select * from bb create trigger tr
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end --若修改bb表num列的值,报错! drop trigger tr
drop table aa,bb 2005试过,报错。
关注。
the row value(s) update or deleted either do not make the row unigue or they alter
muliple rows(2 rows).
set num = num - 1
---
2000不报错
(id int identity(1,1),
num int )
create table aa
(id int identity(1,1),
num int )
insert into aa values(1)
insert into aa values(2)
insert into aa values(3)
select * from aa
insert into bb values(1)
insert into bb values(2)
insert into bb values(3)
select * from bb
go
create trigger tr
on bb
for update
as
if update(num)
begin
update aa
set aa.num=aa.num+inserted.num
from aa,inserted
where aa.id=inserted.id
end
go
update bb
set num = num - 1
--若修改bb表num列的值,报错!
select * from bb
/*
id num
----------- -----------
1 0
2 1
3 2(所影响的行数为 3 行)
*/
drop trigger tr
drop table aa,bb
关注
不会是bug吧!
--1,建触发器的表必须有主键吗?
不必须,但是最好有,保证数据正确性同时可能提高触发器效率 --2, 难道触发器不支持批量更新吗?
支持,只有没写对的触发器才不支持批量更新,不支持批量更新是触发器的常见错误之一 --3,刚刚还好用,现在又不好用了,触发器是不是不稳定?
出了错误应该把错误信息贴出来,这样方便定位错误
不错
LZ 应该没做这一步