有如下表tb1
frk_djlx frk_pjhm frk_hwbh frk_rkrq FRK_JZBZ
采购入库 RK0100_090122091455 总部 2009-01-22 00:00:00.000 1
采购入库 RK4122_090122091985 总部 2009-01-22 00:00:00.000 1
调拨入库 RK0165_090122091935 A1仓 2009-01-22 00:00:00.000 0假如tb1表有新增记录,条件为frk_djlx='采购入库',frk_hwbh <>'A1仓',FRK_JZBZ='1'
那就执行如下如下语句
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
--select语句有视图
我意思是满足条件时,先清除FD_KCXE表,再插入新的记录我写了一个下面这样的触发器,当DJ_SPRKD有满足条件的新增记录时,没有执行INSERT语句,不知怎么回事.找了一些资料好像要用FOR UPDATE,但不知怎么做,请高手指点.
/*
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
if exists(select * from inserted where FRK_DJLX = '零星采购' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
end
end */
frk_djlx frk_pjhm frk_hwbh frk_rkrq FRK_JZBZ
采购入库 RK0100_090122091455 总部 2009-01-22 00:00:00.000 1
采购入库 RK4122_090122091985 总部 2009-01-22 00:00:00.000 1
调拨入库 RK0165_090122091935 A1仓 2009-01-22 00:00:00.000 0假如tb1表有新增记录,条件为frk_djlx='采购入库',frk_hwbh <>'A1仓',FRK_JZBZ='1'
那就执行如下如下语句
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
--select语句有视图
我意思是满足条件时,先清除FD_KCXE表,再插入新的记录我写了一个下面这样的触发器,当DJ_SPRKD有满足条件的新增记录时,没有执行INSERT语句,不知怎么回事.找了一些资料好像要用FOR UPDATE,但不知怎么做,请高手指点.
/*
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
if exists(select * from inserted where FRK_DJLX = '零星采购' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
end
end */
执行这两条语句
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
if exists (select * from inserted where FRK_DJLX = '零星采购' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
end
end
--这样试试。
--注意红字部分。
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
if exists(select * from inserted where FRK_DJLX = N'零星采购' AND FRK_HWBH <> N'售后部' and frk_jzbz='1')
begin
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
end
再看看fd_kcxe 表里有没有触发器?
http://blog.csdn.net/sdhdy/archive/2009/06/07/4249668.aspx
不知是不是这里有问题.CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
if exists(select * from inserted where FRK_DJLX = '采购入库' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
delete from fd_kcxe
insert into fd_kcxe
SELECT GUID_KCXE=NEWID(),GUID_SPBM,FSB_HWBH,FPSL,FPSL FROM NOKIA_FPFS a LEFT JOIN JC_SPBM b on a.fsm_spbm=b.fsm_spbm
end
end
fd_kcxe没有触发器,上面这个触发器在DJ_SPRKD表
首先试下这个!
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
if exists (select * from inserted where FRK_DJLX = '零星采购' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
select 1
end
end
要是选不出来,那就不是执行体的问题了!选出来了就是执行体的问题!
还有再试下这个
CREATE TRIGGER tri_kcfp1 ON DJ_SPRKD
for insert
as
begin
--if exists (select * from inserted where FRK_DJLX = '零星采购' AND FRK_HWBH <> '售后部' and frk_jzbz='1')
begin
select * from inserted--看里面到底有数据没,没有可能就是上面的if exist()的问题了!
end
end
慢慢排错,祝你好运!