Need Trigger Help

[ZRexRider]

Hi,

I have trigger that enforces the creation of a sortorder that is always
1 digit higher than the current highest on Inserts.

This trigger works great if I add one row at a time so I think the
logic is sound. However, I have a Stored Procedure that copies a bunch
of rows into this table and all of the SortOrder values come up as 0.
This stored procedure is doing an "Insert Into" and will insert
numerous rows (10-20) at once.

Since these rows are being inserted is it possible that this trigger
doesn't see the new rows? Is it a timing thing?

Thanks - trigger is below

-------------------------------------------------
ALTER TRIGGER dbo.tblActiveSt ep_SortOrder

ON dbo.tblActiveSt ep

FOR INSERT
AS

-- Declare procedure level constants / variables / objects
----------------------------------------------------------------------------
DECLARE @intNextSortOrd erVal INT
SET NOCOUNT ON
-- Get the MAXimum sort value for steps in Pattern being added
-- and increment by 1
----------------------------------------------------------------------------
BEGIN
SELECT
@intNextSortOrd erVal= MAX(intSortOrde r) + 1 FROM tblActiveStep
WHERE
tblActiveStep.i ntActivePattern ID
IN
(SELECT inserted.intAct ivePatternID FROM inserted)

IF @intNextSortOrd erVal IS NULL
SELECT @intNextSortOrd erVal = 0
-- Set the intSortOrder Value with new calculated value
----------------------------------------------------------------------------
UPDATE
tblActiveStep SET intSortOrder = @intNextSortOrd erVal
WHERE
tblActiveStep.i ntActivePattern ID
IN
(SELECT inserted.intAct ivePatternID FROM inserted)
END
SET NOCOUNT OFF

[Erland Sommarskog]

ZRexRider (je****@ptd.net ) writes:

I have trigger that enforces the creation of a sortorder that is always
1 digit higher than the current highest on Inserts.

This trigger works great if I add one row at a time so I think the
logic is sound. However, I have a Stored Procedure that copies a bunch
of rows into this table and all of the SortOrder values come up as 0.
This stored procedure is doing an "Insert Into" and will insert
numerous rows (10-20) at once.

Since these rows are being inserted is it possible that this trigger
doesn't see the new rows? Is it a timing thing?
No.

It's not clear to me what the trigger is supposed to achieve. What it
would achieve is to set the same sort order for all rows inserted in
the one and same INSERT statement. Recall that a trigger fires once
per statement, not once per row.
SELECT
@intNextSortOrd erVal= MAX(intSortOrde r) + 1 FROM tblActiveStep
WHERE
tblActiveStep.i ntActivePattern ID
IN
(SELECT inserted.intAct ivePatternID FROM inserted)

Apparently this yields NULL in some situations. Since I don't know
the tables, it's a little difficult to say what is happening. Does
intSortOrder have a value on input? Is intActivePatter nID a key,
or could there already be rows with that value. If goes without saying
that if intSortOrder is NULL on INSERT, and you insert a copied new
rows with intActivePatter nID, this SELECT will yeild NULL.

It's always for this kind of problem to post:

o CREATE TABLE statement for the involved table(s). Don't forget the keys.
o INSERT statement with sample data.
o The desired result given the sample data.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ZRexRider]

Thanks - your tip (Trigger fires once per statement not row) tells me
why it doesn't work.

My goal was to add a bunch of rows to a table but I wanted them to
automatically set their sort order to be the next sequential number in
its family.

I.E

Existing demo table

ID Desc SortOrder
1 ABC 0
2 DEF 1
3 CBA 2

Now my query runs and wants to add the following 3 rows

Desc
XXX
YYY
ZZZZ

ID Desc SortOrder
1 ABC 0
2 DEF 1
3 CBA 2
4 XXX 3
5 YYY 4
6 ZZZ 5

Of course... the trigger was working form me every time a record was
manually entered and I don't want to remove the trigger. So, if it
won't work on a bulk insert I'll need to temporarily disable the
trigger during the bulk insert

Thanks for the response

[ZRexRider]

Just in case somebody else Googles this thread, my solution is to
disable the trigger during this multiple insert and re-enable it once
done.

I failed to mention - the data being copied has its own SortOrder
values. I was not including them in the INSERT because I had this
trigger in place to automatically add the sort order and increment it.
I want this trigger because the sort order is important and it nicely
handles the manual rows added in the normal use of the application.
When a user adds a record - it gets sorted to the end. They can use my
interface to move rows up and down but my Adds always go to the end.
Works nicely.

So since I'm copying a set of master records into another table, I
simply disable the SortOrder trigger - insert the rows and enable the
SortOrder trigger.

In my case there is very little risk because the number of users are
small and the person running this "copy" function is pretty much the
same person who will be adding individual rows later. However, there
is always the chance that during this second that the trigger is off -
somebody could sneak in a manualy entered record someplace else. I'm
more likely to get hit by lightning.

Syntax:

ALTER TABLE <table name>
<ENABLE|DISABLE > TRIGGER <ALL|<trigger name>>

[David Portas]

Your "solution" is dangerous and anyway is redundant - why not just
write a trigger that performs correctly with multiple rows instead?

As Erland explained, if you gave us a proper spec I'm sure we could
help you better. In what order would you want to define the sort if
multiple rows were inserted? If you don't care about the order then why
not just default to NULL or 0.

--
David Portas
SQL Server MVP
--

[ZRexRider]

Thanks David

I appreciate your input. Again danger is relative and there is no
redundancy in my solution. It simply copies contents of a
master/template table to and active table.

My requirements are simple:

This portion of my application has a Master/Detail relationship

I have a trigger placed on a field called intSortOrder in the Detail
table so that every individual INSERT into the detail table gets
assigned the next sequential Sort Order. So let's say there are 32
Detail rows associated with Master ID 8. When the user enters a new
item to be associated with item 8, it's sort order will be set to 33.
They will use this method frequently.

The problem arises because I have a special function that lets you
copy/clone a Master and all of it's related detail rows into a similar
pair of tables for special use. The detail table for the receiving
table also wants to enforce the values in the sort order. However,
when I ran the stored procedure it simply gave the intSortOrder value
the same value for each ane every record.

Erland pointed out to me that triggers are do not re-fire for every row
- fire for the Insert statement.

The sort order is important so NULL or 0 is not acceptable.

So although I want the trigger for those frequent manual "adds" done by
my user - it isn't going to help me on the bulk insert.

As per your question: why not just write a trigger that performs
correctly with multiple rows instead?

I have limited experience with triggers - as I learn more I will
certainly do so.

Thanks for the nudge

[Erland Sommarskog]

ZRexRider (je****@ptd.net ) writes:

So although I want the trigger for those frequent manual "adds" done by
my user - it isn't going to help me on the bulk insert.
By default triggers don't fire when you insert data with the BULK INSERT
statement. But it appears that you with "bulk insert" means a plain INSERT
statement that inserts more than row.
As per your question: why not just write a trigger that performs
correctly with multiple rows instead?

I have limited experience with triggers - as I learn more I will
certainly do so.

That's a poor excuse for a bad design. Of course, I have no idea whether
you are doing this as a hobby, or someone is paying you for this.

It still very unclear to me what you are really trying to achieve,
but assuming that the mass-inserted rows already have a sortorder > 1,
this is possible trigger:

CREATE TRIGGER dbo.tblActiveSt ep_SortOrder ON dbo.tblActiveSt ep
FOR INSERT AS

DECLARE @intNextSortOrd erVal INT

SET NOCOUNT ON

IF NOT EXISTS (SELECT *
FROM inserted
WHERE nullif(intSortO rder, 0) IS NULL)
RETURN

IF (SELECT COUNT(*) FROM inserted) > 1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Mult i-row inserts with NULL sortorder not permitted!', 16, 1)
RETURN
END

SELECT @intNextSortOrd erVal = coalesce(MAX(in tSortOrder), 0) + 1
FROM tblActiveStep a
WHERE EXISTS (SELECT *
FROM inserted i
WHERE a.intActivePatt ernID = i.intActivePatt ernID)

UPDATE tblActiveStep
SET intSortOrde = @intNextSortOrd erVal
FROM tblActiveStep a
WHERE EXISTS (SELECT *
FROM inserted i
WHERE a.intActivePatt ernID = i.intActivePatt ernID)
AND nullif(intSortO rder, 0) IS NULL

Still not perfect, but since I don't know the keys of your data, it's
difficult to make it better.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[ZRexRider]

Thanks again for your help and for the time you spent on the code
example. I think you set me straight in your first response where you
pointed out that a trigger does not fire per row but per statement. If
that information is correct, and I believe it is, then there is no
point trying to insert multiple rows and have them receive incrimented
sortorder values via a trigger. As with my original trigger - your
trigger produces the same results - all rows get the value of 1 for
their sort order.

That's a poor excuse for a bad design. Of course, I have no idea whetheryou are doing this as a hobby, or someone is paying you for this.
Thanks for your opinion.
It still very unclear to me what you are really trying to achieve,

I guess it's just too simple to explain. ;-)