SQL Server 触发器示例

最新推荐文章于 2024-08-22 15:49:42 发布

浅尝辄止;

最新推荐文章于 2024-08-22 15:49:42 发布

阅读量753

点赞数

分类专栏： SQL Server 文章标签： SQL Server

本文链接：https://blog.csdn.net/m0_46635910/article/details/124781994

版权

SQL Server 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

SQL Server 触发器示例

Insert触发器示例

USE [GLIS_JZ] 
GO

/****** Object:  Trigger [dbo].[GLIS_Patient_UpRecord]    Script Date: 01/04/2022 15:37:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- 1.0 2022-5-15  tsf    JZ库新增时Net库也加一条数据

ALTER TRIGGER [dbo].[T_Organs_Insert] ON [dbo].[T_Organs] 
FOR  INSERT 
AS

begin
   
     --插入到Net库
     insert into  GLis_Net.dbo.LIS_Common_Code(FID,FName,FParentID,FDescription,FStatus,FCreate,FCreateTime)
      select 'JG' + CONVERT(varchar,i.Id),i.OrganName,'2000',CONVERT(varchar,i.Id), 1 ,'trigger',GETDATE()
      from inserted i
      
end


GO

Update触发器示例：

USE [Glis_Net]
GO

/****** Object:  Trigger [dbo].[GLIS_Patient_UpRecord]    Script Date: 01/04/2022 15:37:41 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- 1.0 2016-10-10 10:50 zmjun
-- 1.1 2019-4-9 15:09 增加'改了名字更新报告表'
-- 1.2 2021-12-30  11:43   同步性别，年龄，年龄单位

ALTER TRIGGER [dbo].[GLIS_Patient_UpRecord] ON [dbo].[GLIS_Patient] 
FOR  UPDATE 
AS
 
-- 患者信息修改时记录
 insert into glis_back.dbo.GLIS_Patient select 'Update',getdate(),* from deleted

-- 改了名字更新报告表
if(UPDATE(FName))
begin
  update LIS_Lab_Bill set FName=i.FName
   from inserted  i
   where LIS_Lab_Bill.FPatientID=i.FPatientID
     and LIS_Lab_Bill.FName!=i.FName     
     
     insert into  LIS_Sys_Log
      select '修改姓名',getdate(),'','','',d.FName+'->'+i.FName,i.FPatientID,'',i.FPatientID
      from inserted i,deleted d
      where i.FPatientID=d.FPatientID and d.FName!=i.FName
end


GO