如何在Sql Server 2008中编写Sql触发器 [英] How Do I Write A Sql Trigger In Sql Server 2008

问题描述

每当更新/更改A列或B列时，我都需要编写一个SQL触发器来使A列= B列。



虽然我在Management Studio工作相当舒服，但我从来没有写过触发器，所以我希望有人能提供一些关于如何做到这一点的基本指导并且可能带有代码示例。



非常感谢任何帮助或建议，



Dana

I have a need to write a SQL trigger to make Column A = Column B whenever Column A OR Column B is Updated/Changed.

Although I'm reasonably comfortable working in Management Studio, I've never written a trigger so I'm hoping someone can provide some basic guidance on how to do this and maybe with a code sample.

Any help or advice greatly appreciated,

Dana

推荐答案

原因很简单，就是了解插入和删除的系统表，然后避免递归循环。



它可能会做得更好，但这是我想出的第一个版本:)

Well of cause the trick is to understand the inserted and deleted system tables and then avoiding the recursive loop.

It can probably be done alot nicer, but this is the first version i came up with :)

create table mytable(
	id int IDENTITY(1,1) PRIMARY KEY,
	columnA nvarchar(5) ,
	columnB nvarchar(5)
	)
GO
CREATE TRIGGER [dbo].[myname]
ON [dbo].[mytable] 
FOR UPDATE
AS
BEGIN
	declare @targets table(
		id int
	)
	insert into @targets(id)
	(select id from deleted)

	declare @id int

	set @id = (select top 1 id from @targets)
	while exists(select * from @targets)
	BEGIN
		declare @a nvarchar(5)
		declare @b nvarchar(5)
		set @a = (select columnA from deleted where id = @id)
		set @b = (select columnB from deleted where id = @id)

		if (Update(columnA) or Update(columnB)) and @a <> @b
		begin
			update mytable
			set columna = columnb
			where id = @id
		end

		delete from @targets where id = @id;
		set @id = (select top 1 id from @targets)
	END
END
go

insert into mytable(ColumnA, ColumnB)
values('abc','bcd')

update mytable
set columna = 'gef'
where id = 1

select * from mytable

drop table mytable

看起来您对介绍性教程感兴趣而不是帮助解决特定问题。在这种情况下，请搜索网络在此处发布问题之前。



也就是说，请尝试阅读触发器 - SQL Server 。

It looks like you're interested in an introductory tutorial rather than help with a specific issue. In such cases, please seach the web before posting a question here.

That said, try reading Triggers -- SQL Server here on CodeProject.

这篇关于如何在Sql Server 2008中编写Sql触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！