RE: Creating a simple Trigger - I have never used or written one before

RE: Creating a simple Trigger - I have never used or written one before

 

  

I'm afraid I'm not with you on your response, Anthony

1. I do understand that RDBMS's use set based logic
2. So does the solution I offered

??

Daniel Morphett
DBA
Brilliant Digital Entertainment
02 9281 9272



-----Original Message-----
From: Thomas Anthony
[mailto:mssqldba-ezmlmshield-x51364858.[Email address protected]
Sent: Saturday, August 28, 2004 3:08 PM
To: LazyDBA Discussion
Subject: RE: Creating a simple Trigger - I have never used or written
one before

NO, NO, NO! Everyone MUST understand that RDBMS's and the bastardized
SQL DBMS's use set-based logic! These persistent row-level triggers
that keep popping up tells me that there must be more education out
there.

My GOD! Get a book and read up on THE Relational Theory!

Here is a better--probably not the best, however:

CREATE TRIGGER dbo.trIU_Properties_TopProperty
ON dbo.Properties
AFTER INSERT, UPDATE

AS

DECLARE @intCount AS INT

SET XACT_ABORT ON
SET NOCOUNT ON

IF UPDATE(TopProperty) BEGIN

SET @intCount =
SELECT COUNT(*)
FROM dbo.Properties
WHERE TopProperty = '1'

IF @intCount > 1 BEGIN
ROLLBACK TRANSACTION
END

END

GO

A few things to mention. In an AFTER trigger, the underlying table has
already been updated and passed any constraint checking. The underlying
table will have all old records and the current value of any modified
records. The INSERTED table will only be the SET of records modified.
So, you could have used the INSERTED table above and be able to confirm
that there was not more than one record modified to have the TopProperty
value set to '1'; however, what if there already existed a record with
that value.

The other point to mention is that if you want to ALWAYS gaurentee that
one, and exactly one, record is set as the TopVAlue, then you could
change the logic above to rollback if the count is not exactly 1.

The last point is that a trigger fires once per statement unless
RECURSIVE TRIGGERS is set on in the database. That one statement can
modify 1 record, the whole table, or any number of rows in between--you
can never guarantee how many. So, you must ALWAYS code as if there were
many results.

Hope this helps.

Sincerely,


Anthony Thomas, MCDBA, MCSA


-----Original Message-----
From: Daniel Morphett
[mailto:mssqldba-ezmlmshield-x49360346.[Email address protected]
Sent: Tuesday, August 24, 2004 8:48 PM
To: LazyDBA Discussion
Subject: RE: Creating a simple Trigger - I have never used or written
one before

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]




---------------------------------------------------------------------
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]




***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************


---------------------------------------------------------------------
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