我有三张表, 一张主表zpurRkdHead,一张从表zpurRkdTail,两表通过id字段关联 ,现写一触发器
目的:当更新主表zpurRkdHead时 若chandler(审核人)字段不为空 则触发器触发 将zpurRkdHead表记录插入godown_entry表中,并计算总价 , 否则不触发。 当更新主表字段chandler 为空时,则从godown表中删除相应记录,下面是具体程序 CREATE TRIGGER update_zpu
ON zpurRkdHead
FOR UPDATE
AS
IF EXISTS(SELECT * FROM INSERTED WHERE chandler IS NOT NULL) BEGIN declare @v_ccode varchar(30)
declare @v_ddate datetime
declare @v_crdcode varchar(5)
declare @v_crdname varchar(12)
declare @v_cvencode varchar(20)
declare @v_cvenabbname varchar(60)
declare @v_cdepcode varchar(12)
declare @v_cdepname varchar(60)
declare @v_cpersoncode varchar(8)
declare @v_cpersonname varchar(20)
declare @v_chandler varchar(20)
declare @v_dveridate datetime
declare @v_iprice money select
@v_ccode = ccode,
@v_ddate = ddate,
@v_crdcode = crdcode,
@v_crdname = crdname,
@v_cvencode = cvencode,
@v_cvenabbname = cvenabbname,
@v_cdepcode = cdepcode,
@v_cdepname = cdepname,
@v_cpersoncode = cpersoncode,
@v_cpersonname = cpersonname,
@v_chandler = chandler,
@v_dveridate = dveridate
from inserted select
@v_iprice = iprice
from (select id,sum(iprice) as iprice from zpurRkdTail group by id) as a,inserted b
where b.id=a.id
and b.chandler is not null
insert into godown_entry
(ccode,ddate,crdcode,crdname,cvencode,cvenabbname,cdepcode,cdepname,cpersoncode,cpersonname,
chandler,dveridate,iprice)
values(@v_ccode,@v_ddate,@v_crdcode,@v_crdname,@v_cvencode,@v_cvenabbname,@v_cdepcode,@v_cdepname,
@v_cpersoncode,@v_cpersonname,@v_chandler,@v_dveridate,@v_iprice) END; 上面是我写的触发器 但是当zpu 这个表 更新 chandler这个字段为null 值时 godown表仍然插入一条记录 也就是触发器并没有判断 什么问题 望高手不吝赐教
目的:当更新主表zpurRkdHead时 若chandler(审核人)字段不为空 则触发器触发 将zpurRkdHead表记录插入godown_entry表中,并计算总价 , 否则不触发。 当更新主表字段chandler 为空时,则从godown表中删除相应记录,下面是具体程序 CREATE TRIGGER update_zpu
ON zpurRkdHead
FOR UPDATE
AS
IF EXISTS(SELECT * FROM INSERTED WHERE chandler IS NOT NULL) BEGIN declare @v_ccode varchar(30)
declare @v_ddate datetime
declare @v_crdcode varchar(5)
declare @v_crdname varchar(12)
declare @v_cvencode varchar(20)
declare @v_cvenabbname varchar(60)
declare @v_cdepcode varchar(12)
declare @v_cdepname varchar(60)
declare @v_cpersoncode varchar(8)
declare @v_cpersonname varchar(20)
declare @v_chandler varchar(20)
declare @v_dveridate datetime
declare @v_iprice money select
@v_ccode = ccode,
@v_ddate = ddate,
@v_crdcode = crdcode,
@v_crdname = crdname,
@v_cvencode = cvencode,
@v_cvenabbname = cvenabbname,
@v_cdepcode = cdepcode,
@v_cdepname = cdepname,
@v_cpersoncode = cpersoncode,
@v_cpersonname = cpersonname,
@v_chandler = chandler,
@v_dveridate = dveridate
from inserted select
@v_iprice = iprice
from (select id,sum(iprice) as iprice from zpurRkdTail group by id) as a,inserted b
where b.id=a.id
and b.chandler is not null
insert into godown_entry
(ccode,ddate,crdcode,crdname,cvencode,cvenabbname,cdepcode,cdepname,cpersoncode,cpersonname,
chandler,dveridate,iprice)
values(@v_ccode,@v_ddate,@v_crdcode,@v_crdname,@v_cvencode,@v_cvenabbname,@v_cdepcode,@v_cdepname,
@v_cpersoncode,@v_cpersonname,@v_chandler,@v_dveridate,@v_iprice) END; 上面是我写的触发器 但是当zpu 这个表 更新 chandler这个字段为null 值时 godown表仍然插入一条记录 也就是触发器并没有判断 什么问题 望高手不吝赐教
ON zpurRkdHead
FOR UPDATE
AS
BEGIN insert into godown_entry
(ccode,ddate,crdcode,crdname,cvencode,cvenabbname,cdepcode,cdepname,cpersoncode,cpersonname,
chandler,dveridate,iprice)
select
b.ccode,
b.ddate,
b.crdcode,
b.crdname,
b.cvencode,
b.cvenabbname,
b.cdepcode,
b.cdepname,
b.cpersoncode,
b.cpersonname,
b.chandler,
b.dveridate
, iprice
from (select id,sum(iprice) as iprice from zpurRkdTail group by id) as a,inserted b
where b.id=a.id
and b.chandler is not null
END;
INSERT TBTEST
SELECT 1 ,'FDASF' UNION ALL
SELECT 2 ,'EWREW' UNION ALL
SELECT 3 ,'JHGGGGFJ'CREATE TABLE TBTEST1(ID INT)
--SELECT * FROM TBTESTCREATE TRIGGER TRI ON TBTEST FOR UPDATE
AS
BEGIN
IF EXISTS(SELECT 1 FROM INSERTED WHERE STRING IS NOT NULL)
INSERT TBTEST1 SELECT ID FROM INSERTED
END --DROP TRIGGER TRIUPDATE TBTEST SET STRING=NULL WHERE STRING='JHGGGGFJ'SELECT * FROM TBTEST1我这样都行啊?