修改触发器
程序员文章站
2023-12-27 11:40:39
...
修改触发器 SQLServer JDBC Driver 监测某张表修改后自动修改另一张表的数据:cf_userinfo的表结构:CREATE TABLE [dbo].[cf_userinfo] ([id] int NOT NULL IDENTITY(1,1) ,[uid] int NOT NULL ,[name] varchar(50) COLLATE Chinese_PRC_CI_AS NULL ,CONSTRAINT
修改触发器 SQLServer JDBC Driver监测某张表修改后自动修改另一张表的数据: cf_userinfo的表结构: CREATE TABLE [dbo].[cf_userinfo] ( [id] int NOT NULL IDENTITY(1,1) , [uid] int NOT NULL , [name] varchar(50) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK__cf_userinfo__5535A963] PRIMARY KEY ([id]) ) ON [PRIMARY] GO userinfo表的结构: CREATE TABLE [dbo].[userinfo] ( [id] int NOT NULL IDENTITY(1,1) , [name] varchar(50) COLLATE Chinese_PRC_CI_AS NULL , CONSTRAINT [PK__userinfo__534D60F1] PRIMARY KEY ([id]) ) ON [PRIMARY] GO 创建触发器对userinfo表的触发器 create trigger userinfoupdate on userinfo for update --监测userinfo表修改 as if update(name) --如果name字段修改 begin print '触发userinfo表修改事件,修改cf_userinfo数据中……'; declare @id INT,@newName varchar(50), @UID INT; --更新前的userinfo表的ID select @id = id from deleted; if (exists (select * from cf_userinfo where uid =@id)) begin --更新后的userinfo表的name字段 select @newName = name from inserted; --更新后的userinfo表的ID字段 select @UID = id from deleted; update cf_userinfo set cf_userinfo.name=@newName from userinfo where cf_userinfo.uid=@UID end else print '无需修改cf_userinfo表' end