a表:Fields
b表:Customeres
--添加数据时的触发器
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create TRIGGER [trigger_addField] ON [dbo].[Fields]
FOR INSERT
AS
DECLARE @FieldID int,
@Name varchar(50),
@DataType varchar(50),
@SQL varchar(1000)
SELECT @FieldID = FieldID,
@Name = [Name],
@DataType = 'varchar(64)'
FROM Inserted
if not exists (SELECT * FROM syscolumns where id=object_id('Customeres') AND name=@Name)
BEGIN
SET @SQL = 'ALTER table Customeres add ' + @Name + ' '+ @DataType
EXEC (@SQL)
END
PRINT
@Name + ',' + @DataType
--改变数据时的触发器
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
alter trigger [tgr_updateField]
on [dbo].[Fields]
for update
as declare @oldName varchar(20), @newName varchar(20),@aa varchar(20),@SQL varchar(1000) ;
--更新前的数据
select @oldName = name from deleted;
--更新后的数据
select @newName = name from inserted;
if not exists (SELECT * FROM syscolumns where id=object_id('Customeres') AND name=@newName)
select @aa='Customeres.'+@oldName;
BEGIN
EXEC sp_rename @aa, @newName,'COLUMN';
END
--删除数据时的触发器
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create TRIGGER [trigger_deletField] ON [dbo].[Fields]
FOR delete
AS
DECLARE
@Name varchar(50),
@SQL varchar(1000);
SELECT @Name = [Name] from deleted;
if exists (SELECT * FROM syscolumns where id=object_id('Customeres') AND name=@Name)
BEGIN
SET @SQL = 'alter table Customeres DROP COLUMN ' + @Name
EXEC (@SQL)
END