sql server2000触发器问题
同一数据库有表a,表b；
a表字段结构：编号(int) 名称(varchar(20)) 委托单号(varchar(20)) 手机(varchar(11)) 状态(varchar(10))
1 a1 N001 13812345678 在检
2 a2 N001 13812345678 完检
3 b11 N005 13887654321 完检
4 b12 N005 13887654321 完检
5 c1 N005 13887654321 完检
6 b111 N123 13887654320 完检
b表字段结构：id(int) 委托单号(varchar(20)) 手机(varchar(11))
现在要求在a表写一触发器，相同委托单号的记录，如果状态均为‘完检’则将a表中的委托单号和手机写入b表中
上例的依据a表记录要求得到的b表记录如下
委托单号 手机
N005 13887654321
N123 13887654320
[最优解释]
create trigger tri_insert
on [9DEC_goods]
for insert
as
begin
declare @id varchar(300),@num int,@num2 int
set @num=0
set @num2=0
select @id=委托单号 from inserted
select @num=count(1) from 表a where 委托单号=@id
select @num2=count(1) from 表a where 委托单号=@id and 状态='完检'
if @num != 0
begin
if @num=@num2
begin
--插入表2
end
end
end
--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([编号] INT,[名称] VARCHAR(4),[委托单号] VARCHAR(4),[手机] BIGINT,[状态] VARCHAR(4))
INSERT [tb]
SELECT 1,'a1','N001',13812345678,'在检' UNION ALL
SELECT 2,'a2','N001',13812345678,'完检' UNION ALL
SELECT 3,'b11','N005',13887654321,'完检' UNION ALL
SELECT 4,'b12','N005',13887654321,'完检' UNION ALL
SELECT 5,'c1','N005',13887654321,'完检' UNION ALL
SELECT 6,'b111','N123',13887654320,'完检'
--------------开始查询--------------------------
SELECT * FROM [tb] AS t,INSERTED AS i where a.[委托单号]=i.[委托单号]
WHERE NOT EXISTS
(SELECT 1 FROM [tb] WHERE [委托单号]=t.[委托单号] AND [状态]='在检')
----------------结果----------------------------
/*
*/
CREATE TRIGGER tt ON a
FOR INSERT , UPDATE
AS
BEGIN
--判断是否符合条件
IF EXISTS ( SELECT *
FROM [a] AS t ,
INSERTED AS i
WHERE t.[委托单号] = i.[委托单号]
AND NOT EXISTS ( SELECT 1
FROM [a]
WHERE [委托单号] = t.[委托单号]
AND [状态] != '完检' ) )
BEGIN
--判断b表是否已存在
IF NOT EXISTS ( SELECT *
FROM [b] ,
INSERTED AS i
WHERE b.[委托单号] = i.[委托单号] )
BEGIN
INSERT INTO b
SELECT 委托单号 , 手机
FROM INSERTED
END
END
END
CREATE TRIGGER T_INS ON A
FOR INSERT
AS
BEGIN
INSERT INTO B ( 委托单号,手机)
SELECT 委托单号,手机 FROM INSERTED A WHERE 状态='完检'AND NOT EXISTS(SELECT 手机 FROM B WHERE 委托单号=A.委托单号 AND 手机=A.手机 ) GROUP BY 委托单号,手机
END
