表单触发器

xnaigee

于 2022-07-06 16:08:11 发布

阅读量116

点赞数

分类专栏： windows 文章标签：大数据

本文链接：https://blog.youkuaiyun.com/xnaigee/article/details/125641913

版权

windows 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了两个数据库触发器的使用，第一个用于在大数据背景下控制审核单据并更改物料单价，当特定条件满足时更新物料价格记录。第二个触发器则在单据保存时进行权限检查，防止在查询帐套中保存数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

控制审核单据更改物料单价

ALTER TRIGGER [dbo].[ICStockBill_updateprice]
ON [dbo].[ICStockBill]
FOR update
AS

SET NOCOUNT ON

if exists (select 1 from deleted where ftrantype =1 and frob=1 and isnull(fcheckerid,0)=0)
begin
if exists (select 1 from inserted where ftrantype =1 and frob=1 and isnull(fcheckerid,0)>0)
begin

      declare @fitemid int
      declare @fprice decimal(18, 4)
      declare @forderprice decimal(18, 4)

      select @fitemid=tt.fitemid,@fprice=tt.fprice,@forderprice=t1.forderprice from icstockbillentry tt
      left join t_ICItemCore t1 on tt.fitemid=t1.fitemid
      where finterid in (select finterid from inserted)

INSERT INTO zt_wg_updatepricetime(fitemid,forderprice_old,forderprice_new,ftype,fupdatetime)
VALUES(@fitemid,@forderprice,@fprice,0,getdate())

      update a set a.forderprice=b.fprice
            from t_ICItemCore a,(select fitemid,fprice from icstockbillentry where finterid in (select finterid from inserted)) b
            where a.fitemid=b.fitemid
        end
end

控制单据保存

ALTER TRIGGER [dbo].[SEOutStock_save]
ON [dbo].[SEOutStock]
FOR insert
AS
declare @finterid int
select @finterid=finterid from inserted
if exists (select * from inserted a,seoutstockentry b
where a.finterid=b.finterid
and b.finterid=@finterid
and a.fdate>'2010-01-08')
begin
rollback tran
raiserror ('帐套为查询帐套不允许保存数据',18,18)
end