为什么触发器,不是当审核时更新库存,而是保存时就更新库存?
偶的思路如下:
这是表的结构
tjc表(偶自建的表,用来初始化各物料的结存数量)
FNumber(物料号) FQty(初始数量)
7.01.01.01.0021 1000ICStockBillEntry表(生产任务单分录表,综合了生产入库、生产领料十多个前台界面的数据表,我插)
FItemID(物料号内码) FInterID(入库单据内码) FSourceTranType(厂内生产任务单内码) FPPBomEntryID(投料单序列号)
0001 0001 85 0
FQty(入库数量)
50说明:FSourceTranType当为85时才是厂内,若为其它,可能为委外,所以必须限定,FPPBomEntryID必须限定为0,才是生产任务单的产品,若为1,2则是生产投料所需的原料。ICStockBill(生产任务单主表)
FInterID(入库单据内码) FStatus(审核标志,若0则为未审核,1则为审核)
0001 0t_ICItemCore(物料表)
FItemID(物料号内码) FNumber(物料长代码)
0001 7.01.01.01.0021
create trigger t_tjc on ICStockBillEntry
for insert
as
set nocount on
BEGIN
DECLARE @FItemID int
select @FItemID=FItemID from inserted
if exists(select * from ICStockBillEntry a
left outer join ICStockBill d on a.FInterID=d.FInterID
left outer join t_ICItemCore b on a.FItemID=b.FItemID
left outer join tjc c on b.FNumber=c.FNumber
where a.FSourceTranType=85 and a.FPPBomEntryID=0 and d.FStatus=1 and a.FItemID=@FItemID)
update A
set A.FQty=case when D.FStatus=1 then A.FQty-D.FQty else A.FQty end
from tjc A
left outer join t_ICItemCore E on A.FNumber=E.FNumber
left outer join
(select B.FQty,B.FPPBomEntryID,B.FItemID,B.FSourceTranType,D.FStatus from ICStockBillEntry B left outer join t_ICItemCore C on B.FItemID=C.FItemID
left outer join ICStockBill D on B.FInterID=D.FInterID where B.FSourceTranType=85 and B.FPPBomEntryID=0 and D.FStatus=1 and B.FItemID=@FItemID) D
on E.FItemID=D.FItemID where D.FSourceTranType=85 and D.FStatus=1 and D.FItemID=@FItemID
END
set nocount off
现在偶想要的结果是,当在生产入库单插入物料号,生产入库数量时,当审核后,才将tjc表的数量相减,而现在出现的问题是,我一单击K3前台界面的保存按钮,tjc表就会减去入库数量,而不是审核后才会相减,偶自己新建的二个表,用同样的原理做时,这样就OK,请高手指点:if object_id('test') is not null drop table test
if object_id('t_test1') is not null drop trigger t_test1create trigger t_test1 on test
as
begin
declare @FNumber varchar(40)
select @FNumber=FNumber from inserted
if exists(select * from test where FNumber=@FNumber)
update A
set A.FQty=A.FQty-B.FQty
from tjc A left outer join test B on A.FNumber=B.FNumber and B.FNumber=@FNumbercreate table test (FNumber varchar(40),FQty(40))
insert test
select '7.01.01.01.0021',50
结果是tjc的FQty列会自动减少50,请高手指导
偶的思路如下:
这是表的结构
tjc表(偶自建的表,用来初始化各物料的结存数量)
FNumber(物料号) FQty(初始数量)
7.01.01.01.0021 1000ICStockBillEntry表(生产任务单分录表,综合了生产入库、生产领料十多个前台界面的数据表,我插)
FItemID(物料号内码) FInterID(入库单据内码) FSourceTranType(厂内生产任务单内码) FPPBomEntryID(投料单序列号)
0001 0001 85 0
FQty(入库数量)
50说明:FSourceTranType当为85时才是厂内,若为其它,可能为委外,所以必须限定,FPPBomEntryID必须限定为0,才是生产任务单的产品,若为1,2则是生产投料所需的原料。ICStockBill(生产任务单主表)
FInterID(入库单据内码) FStatus(审核标志,若0则为未审核,1则为审核)
0001 0t_ICItemCore(物料表)
FItemID(物料号内码) FNumber(物料长代码)
0001 7.01.01.01.0021
create trigger t_tjc on ICStockBillEntry
for insert
as
set nocount on
BEGIN
DECLARE @FItemID int
select @FItemID=FItemID from inserted
if exists(select * from ICStockBillEntry a
left outer join ICStockBill d on a.FInterID=d.FInterID
left outer join t_ICItemCore b on a.FItemID=b.FItemID
left outer join tjc c on b.FNumber=c.FNumber
where a.FSourceTranType=85 and a.FPPBomEntryID=0 and d.FStatus=1 and a.FItemID=@FItemID)
update A
set A.FQty=case when D.FStatus=1 then A.FQty-D.FQty else A.FQty end
from tjc A
left outer join t_ICItemCore E on A.FNumber=E.FNumber
left outer join
(select B.FQty,B.FPPBomEntryID,B.FItemID,B.FSourceTranType,D.FStatus from ICStockBillEntry B left outer join t_ICItemCore C on B.FItemID=C.FItemID
left outer join ICStockBill D on B.FInterID=D.FInterID where B.FSourceTranType=85 and B.FPPBomEntryID=0 and D.FStatus=1 and B.FItemID=@FItemID) D
on E.FItemID=D.FItemID where D.FSourceTranType=85 and D.FStatus=1 and D.FItemID=@FItemID
END
set nocount off
现在偶想要的结果是,当在生产入库单插入物料号,生产入库数量时,当审核后,才将tjc表的数量相减,而现在出现的问题是,我一单击K3前台界面的保存按钮,tjc表就会减去入库数量,而不是审核后才会相减,偶自己新建的二个表,用同样的原理做时,这样就OK,请高手指点:if object_id('test') is not null drop table test
if object_id('t_test1') is not null drop trigger t_test1create trigger t_test1 on test
as
begin
declare @FNumber varchar(40)
select @FNumber=FNumber from inserted
if exists(select * from test where FNumber=@FNumber)
update A
set A.FQty=A.FQty-B.FQty
from tjc A left outer join test B on A.FNumber=B.FNumber and B.FNumber=@FNumbercreate table test (FNumber varchar(40),FQty(40))
insert test
select '7.01.01.01.0021',50
结果是tjc的FQty列会自动减少50,请高手指导
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。在执行 DELETE 或 UPDATE 语句时,行从触发器表中删除,并传输到 deleted 表中。Deleted 表和触发器表通常没有相同的行。
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。在一个插入或更新事务处理中,新建行被同时添加到 inserted 表和触发器表中。Inserted 表中的行是触发器表中新行的副本。
更新事务类似于在删除之后执行插入;首先旧行被复制到 deleted 表中,然后新行被复制到触发器表和 inserted 表中。
就是在审核时,要更新 FStatus(审核标志,若0则为未审核,1则为审核) ,触发
而不是insert 触发器
应该在ICStockBill 上
这只是偶个人做的触发器更新偶的自建表,触发器有问题:
create trigger t_tjc on ICStockBillEntry
for insert
as
set nocount on
BEGIN
DECLARE @FItemID int
select @FItemID=FItemID from inserted
if exists(select * from ICStockBillEntry a
left outer join ICStockBill d on a.FInterID=d.FInterID
left outer join t_ICItemCore b on a.FItemID=b.FItemID
left outer join tjc c on b.FNumber=c.FNumber
where a.FSourceTranType=85 and a.FPPBomEntryID=0 and d.FStatus=1 and a.FItemID=@FItemID) update A
set A.FQty=case when D.FStatus=1 then A.FQty-D.FQty else A.FQty end
from tjc A
left outer join t_ICItemCore E on A.FNumber=E.FNumber
left outer join
(select B.FQty,B.FPPBomEntryID,B.FItemID,B.FSourceTranType,D.FStatus from ICStockBillEntry B left outer join t_ICItemCore C on B.FItemID=C.FItemID
left outer join ICStockBill D on B.FInterID=D.FInterID where B.FSourceTranType=85 and B.FPPBomEntryID=0 and D.FStatus=1 and B.FItemID=@FItemID) D
on E.FItemID=D.FItemID where D.FSourceTranType=85 and D.FStatus=1 and D.FItemID=@FItemID END
set nocount off
ICStockBill是主表,相当于一张生产入库单的“单头”部分,而ICStockBillEntry是分录表,相当于“单身和单尾”部分
我不管你说的这些东西现在 ,你下面这个写法,肯定是在插入时(就是你点保存时),触发,就是更新的呀
create trigger t_tjc on ICStockBillEntry
for insert
for update ---更新
update触发器在更新时出发 如果你审核时触发可以见触发器
create trigger t_tjc on ICStockBillEntry
for update
as
................
如果在审核时更新审核日期可以这样写
create trigger t_tjc on ICStockBillEntry
for update
as
if update(列名)--审核时可能是更新审核日期 审核人啥的
begin
--你的操作
end
CREATE TRIGGER [触发器名称] ON [dbo].[表名]
FOR INSERT
AS
BEGIN
DECLARE @id int
SELECT @id=id from inserted
select 1 --正文
END--instead of (等同于代替insert、delete或update操作。虽然,这时候inserted或deleted两个特殊表里有数据,但是并没有对本表数据进行insert、delete或update操作,这样的话,还要在触发器里手工进行对本表insert、delete或update的操作)
CREATE TRIGGER [触发器名称] ON [dbo].[表名]
Instead of INSERT
AS
BEGIN
DECLARE @id int
SELECT @id=id from inserted
select 1 --正文
END
上面用我的
create trigger t_tjc on ICStockBill
for update ---更新 as 。下面是你触发的代码,试试
drop table tb
go
create table tb(id int, name nvarchar(20))
goinsert into tb select 1,null
select * from tb
/*
id name
----------- --------------------
1 NULL
*/
--建立insert触发器
if object_id('tr_insert','tr') is not null
drop trigger tr_insert
go
create trigger tr_insert on tb
for insert
as
update tb set id =999 where id=(select top 1 id from inserted)
go
insert into tb select 2,null --触发插入触发器
select * from tb
/*查询结果
id name
----------- --------------------
1 NULL
999 NULL
(所影响的行数为 2 行)
*/
drop trigger tr_insert
--建立update触发器
if object_id('tr_update','tr') is not null
drop trigger tr_update
go
create trigger tr_update on tb
for update
as
if update(name) --更新name字段
update tb set name ='update' where id=(select top 1 id from inserted)
go
insert into tb select 3,null --触发插入触发器
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 NULL--
*/
update tb set name='hello world' where id =3
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 update
*/
给你写了个例子你看一下 if object_id('tb') is not null
drop table tb
go
create table tb(id int, name nvarchar(20))
goinsert into tb select 1,null
select * from tb
/*
id name
----------- --------------------
1 NULL
*/
--建立insert触发器
if object_id('tr_insert','tr') is not null
drop trigger tr_insert
go
create trigger tr_insert on tb
for insert
as
update tb set id =999 where id=(select top 1 id from inserted)
go
insert into tb select 2,null --触发插入触发器
select * from tb
/*查询结果
id name
----------- --------------------
1 NULL
999 NULL
(所影响的行数为 2 行)
*/
drop trigger tr_insert
--建立update触发器
if object_id('tr_update','tr') is not null
drop trigger tr_update
go
create trigger tr_update on tb
for update
as
if update(name) --更新name字段
update tb set name ='update' where id=(select top 1 id from inserted)
go
insert into tb select 3,null --触发插入触发器
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 NULL--
*/
update tb set name='hello world' where id =3
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 update
*/
学习
给你写了个例子你看一下 if object_id('tb') is not null
drop table tb
go
create table tb(id int, name nvarchar(20))
goinsert into tb select 1,null
select * from tb
/*
id name
----------- --------------------
1 NULL
*/
--建立insert触发器
if object_id('tr_insert','tr') is not null
drop trigger tr_insert
go
create trigger tr_insert on tb
for insert
as
update tb set id =999 where id=(select top 1 id from inserted)
go
insert into tb select 2,null --触发插入触发器
select * from tb
/*查询结果
id name
----------- --------------------
1 NULL
999 NULL
(所影响的行数为 2 行)
*/
drop trigger tr_insert
--建立update触发器
if object_id('tr_update','tr') is not null
drop trigger tr_update
go
create trigger tr_update on tb
for update
as
if update(name) --更新name字段
update tb set name ='update' where id=(select top 1 id from inserted)
go
insert into tb select 3,null --触发插入触发器
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 NULL--
*/
update tb set name='hello world' where id =3
select * from tb
/*
id name
----------- --------------------
1 NULL
999 NULL
3 update
*/