触发器应用实例 返回当前数据库表格中的表格名称和触发器名称

use testdb

select * from sys.tables   ---所有用户自定义的表
select * from sysobjects   where xtype='tr'  ----返回当前数据库的所有的触发器

select t.name  tablename,s.name triggername from sys.tables  t
    join sysobjects  s on  t.object_id =s.parent_obj
    where xtype=N'tr'
    order by t.name
/*返回当前数据库表格中的表格名称和触发器名称*/
 

  sp_helptext 'gcheckU'   ---查看指定触发器的内容

 

1、数据一致性

USE testdb
GO

SET QUOTED_IDENTIFIER ON
GO

 

create  TRIGGER [dbo].[gCustomerU] ON [dbo].[tCustomer] 
FOR UPDATE 
AS
Begin
Set NoCount On
--更新往来帐目表的期初数
If Update(fOriginSum) or Update(fRemain)
    Update a set a.fPaySum=i.fOriginSum,a.fRemain=i.fRemain
        from tAccountC a,Inserted i where a.fCustomerID=i.fID

Set NoCount On
End

Create TRIGGER [dbo].[gCustomerI] ON [dbo].[tCustomer] 
FOR INSERT
AS
Begin
Declare @Date Datetime
Set NoCount On

Set @Date=dbo.fuser_formatDate(Getdate())

--在客户往来帐表中插如相应记录
    Insert Into tAccountC(fCustomerID,fLastDate,fPaySum,fRemain)
        Select fID,@Date,fOriginSum,fRemain from Inserted
Set NoCount Off
End

 

USE [ClothingTem]
GO
/****** Object:  Trigger [dbo].[gChargeCustomerU]    Script Date: 10/22/2019 19:56:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 

CREATE  TRIGGER [dbo].[gChargeCustomerU] ON [dbo].[tChargeCustomer] 
FOR UPDATE
AS
Begin
Set NoCount On
Declare @OldSubmitFlag bit,@NewSubmitFlag bit

if Update(fSubmitFlag)
begin
Select @OldSubmitFlag=fSubmitFlag from Deleted
Select @NewSubmitFlag=fSubmitFlag from Inserted
if (@OldSubmitFlag=0) and (@NewSubmitFlag=1)
begin
    update a set a.fPaySum=a.fPaySum+I.fSum
        from tAccountC a,Inserted I
        where a.fCustomerID=I.fCustomerID

    --记入往来帐
    Insert Into tCustomerAccountIO(fDate,fCustomerID,fType,fOrderNO,fDescription,fIncSumPrice,fPaySum,fRemain)
        Select GetDate(),I.fCustomerID,3,I.fNO,I.fDescription,I.fSum,a.fPaySum,a.fRemain
        from Inserted I,tAccountC a
        Where i.fCustomerID=a.fCustomerID       
end
end
end