查了一些资料,还是不好写这样的一个触发器,请高手帮忙。
是这样的,A 表是主表格式如下:uid username age email ...
...5 张三 20 [email protected]
...A 表中uid 是自动生成的,我把它做为客户编号,想要在A表生成时就加到其他表(如下面的B,C表中),如何写这个触发器,其它表格式如下:例如 B 表:(id 自动生成)id uid username photopath ....1 5 张三 /UPLOAD/IMAGES1例如 C 表:(id 自动生成):id uid username games gameimage1 5 张三 SHOTING /UPDLOAD/SHOTINGIMAGE
是这样的,A 表是主表格式如下:uid username age email ...
...5 张三 20 [email protected]
...A 表中uid 是自动生成的,我把它做为客户编号,想要在A表生成时就加到其他表(如下面的B,C表中),如何写这个触发器,其它表格式如下:例如 B 表:(id 自动生成)id uid username photopath ....1 5 张三 /UPLOAD/IMAGES1例如 C 表:(id 自动生成):id uid username games gameimage1 5 张三 SHOTING /UPDLOAD/SHOTINGIMAGE
解决方案 »
- 几百万数据的update
- SQL 的ceiling函数和程序里的运算,效率问题!
- SQL2000如何导入数据
- 谁懂执行计划??????????????????进来看看
- 还原数据库
- 请比较2句SQL有什么不同,急!!!!!!!!!
- 高分:求修改表中所有字段 NULL 改为 NOT NULL
- 本单位数据库因硬盘错误,恢复不了数据库,求救!
- 哪位能给一个存储过程代码及执行语句的例子呀?(要带输入各输出参数哦)
- 我的数据库服务器为SQL SERVER 7.0+SP2/(NT SERVER 4.0+SP6),我现在想在该台服务器上增加一个CPU,请问SQL SERVER 会不会自动识别,会不会造成SQL SERVER的不稳定?
- 求问TreeView 在SQL查询语句的使用
- 在线急等高手援助,语句有问题执行不了
什么意思呢?
A表生成时,把什么加到其他表
客户编号uid 加到其他表。
CREATE TABLE TABLE_1
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)
if object_id('TABLE_2') is not null drop table TABLE_2
CREATE TABLE TABLE_2
(
ID INT primary key,
Name1 nchar(10),
Name2 nchar(10)
)INSERT INTO Table_1 VALUES(1,'adsd','Chi')
INSERT INTO Table_2 VALUES(1,'Lei','Chi')
INSERT INTO Table_2 VALUES(2,'Alex','Chi')create trigger tr_info on TABLE_2
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)--insert触发器
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted)--update触发器 begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
--插入测试INSERT INTO Table_2 VALUES(3,'huguo','Chi')
select * from TABLE_1ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi
3 huguo Chi (3 行受影响)--删除测试
delete from Table_2 where ID=3ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 Alex Chi (2 行受影响)
--更新测试select * from Table_2update Table_2 set Name1='ALex2' where ID=2ID Name1 Name2
----------- ---------- ----------
1 adsd Chi
2 ALex2 Chi (2 行受影响)本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/ws_hgo/archive/2010/09/15/5885370.aspx
不行啊,我修改如下,看看那里有错呢?create trigger tr_informat919 on ladlady
for insert,update,delete
as
begin
if exists(select * from inserted) and not exists(select * from deleted)
begin
insert into customer1 select * from inserted
end
else if exists(select * from inserted) and exists(select * from deleted)
begin
update b1
set b1.uid=U.uid
from customer1 b1,deleted U
where b1.uid=U.ID
end
else
begin
delete customer1 where uid=(select uid from deleted)
end
end有错误提示:服务器: 消息 213,级别 16,状态 4,过程 tr_informat919,行 7
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 311,级别 16,状态 1,过程 tr_informat919,行 7
不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted) begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into Table_1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted) begin
update b1
set b1.Name1=U.Name1,b1.Name2=U.Name2
from Table_1 b1,deleted U
where b1.ID=U.ID
end
else
begin
delete Table_1 where ID=(select ID from deleted)
end
end
修改成如下,按我的表,customer1 没有ID,只有uid(自动由表生成)
create trigger tr_info on ladlady
for insert,update,delete
as
begin
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
insert into customer1 select * from inserted
end
else if exists(select 1 from inserted) and exists(select 1 from deleted) begin
update b1
set b1.uid=U.uid,b1.Username=U.USERNAME
from customer1 b1,deleted U
where b1.uid=U.id
end
else
begin
delete customer1 where uid=(select uid from deleted)
end
end运行后有错误代码如下:
服务器: 消息 213,级别 16,状态 4,过程 tr_info,行 7
插入错误: 列名或所提供值的数目与表定义不匹配。
服务器: 消息 311,级别 16,状态 1,过程 tr_info,行 7
不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。请帮忙修改一下。