CREATE TRIGGER [tr_table1] ON [dbo].[Table1]
FOR INSERT, UPDATE
AS
declare @a varchar, @id int
select @a = a, @id = id from inserted

if @a = 1
update table1 set a = a + 1
where a = 1 and id != @id

this assumes you have a unique key on the table (the id col)

Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272



-----Original Message-----
From: ces
[mailto:mssqldba-ezmlmshield-x62000886.[Email address protected]
Sent: Wednesday, August 25, 2004 6:09 AM
To: LazyDBA Discussion
Subject: Creating a simple Trigger - I have never used or written one
before

I need to create a trigger that ensures that 1 and only 1 field in one
of my tables is ever set to "1" or true.
(and I'm using a varchar for that field not a boolean..if that
matters??)

So I have a table called "Properties" and a field in that called
"TopProperty". I want to create a trigger to ensure that

no matter how many records are added to this table, that only 1 record
at any one time ever has the value of "1" in the

"TopProperty" field. That field will only be explicitly set on an
OnClick event.

any help would be greatly appreciated.

Thanks,
Gabe


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]



MS Sql Server LazyDBA home page