表单触发器

最新推荐文章于 2022-11-19 10:28:43 发布

xnaigee

最新推荐文章于 2022-11-19 10:28:43 发布

阅读量135

点赞数

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

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

版权

windows 同时被 2 个专栏收录

5 篇文章

订阅专栏

数据库

3 篇文章

订阅专栏

这篇博客介绍了如何使用SQL触发器来控制大数据表单的反审核和价格范围审核。通过创建`seoutstock_fcheck`触发器防止2013年以前的数据反审核，以及`seoutstock_checkJ`触发器来确保价格在指定范围内，从而确保数据的正确性和安全性。

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

控制时间的单据不让反审核

create TRIGGER [dbo].[seoutstock_fcheck] ON [dbo].[SEOutStock]
FOR update
AS
SET NOCOUNT ON

if exists (select 1 from deleted where fdate<'2013-01-01' and isnull(fcheckerid,0)<>0)
begin
if exists (select 1 from inserted where fdate<'2013-01-01' and isnull(fcheckerid,0)=0)
begin

              DECLARE @FStatus int
                    DECLARE @Finterid   int

               select @FStatus=fcheckerid,@Finterid=finterid from seoutstock
               where finterid in (select finterid from inserted)
                     if isnull(@FStatus,0)=0
                     begin
               ROLLBACK TRAN
                    RAISERROR('2013年以前数据，无法反审单据，请冲红!',18,18)
                     end
        end
END

控制价格范围审核

ALTER TRIGGER [dbo].[seoutstock_checkJ]
   ON [dbo].[SEOutStock]
   FOR update
AS
BEGIN
            DECLARE @aa decimal(18, 4)

/* if exists (select 1 from deleted where ftrantype =83 and isnull(fcheckerid,0)=0)
begin
if exists (select 1 from inserted where ftrantype =83 and isnull(fcheckerid,0)<>0)
begin

            DECLARE @fprice decimal(18, 4)
            DECLARE @fcheckerid   int
   declare @zx1 decimal(18, 4)
   declare @zx3 decimal(18, 4)

select @fcheckerid=fcheckerid from inserted

--            select @fprice = t1.fprice,@zx1=t1.fentryselfs0235,@zx3=t1.fentryselfs0237
--   from seoutstock tt left join SEOutStockentry t1 on tt.finterid=t1.finterid
--         left join t_SubMessage t2 on t2.finterid=(select ftypeid from t_Organization where fitemid=tt.fcustid) where tt.finterid=(select finterid from inserted) and t1.fnote <> '赠品'

   if @fcheckerid not in( 17192 , 17094,16791)
   begin
                if isnull(@zx3,0)<>0 or isnull(@zx1,0)<>0
                begin
     if exists (select 1 from seoutstock tt left join SEOutStockentry t1 on tt.finterid=t1.finterid left join t_SubMessage t2 on t2.finterid=(select ftypeid from t_Organization where fitemid=tt.fcustid) where tt.finterid=(select finterid from inserted) and t1.fnote <> '赠品' and (t1.fentryselfs0235-t1.fprice)>0 and isnull(fentryselfs0235,0) <> 0)
      begin
      ROLLBACK TRAN
      RAISERROR('低于ZX1价，您没有权限审核!',18,18)
      end

     if exists (select 1 from seoutstock tt left join SEOutStockentry t1 on tt.finterid=t1.finterid left join t_SubMessage t2 on t2.finterid=(select ftypeid from t_Organization where fitemid=tt.fcustid) where tt.finterid=(select finterid from inserted) and t1.fnote <> '赠品' and (t1.fentryselfs0237-t1.fprice)<0 and isnull(fentryselfs0237,0) <> 0)
      begin
      ROLLBACK TRAN
      RAISERROR('高于ZX3价，您没有权限审核!',18,18)
      end
                end
   end
        end
end