Trigger - very basic problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger - very basic problem

Author

Topic

DaveA
Starting Member

6 Posts

Posted - 2005-08-28 : 11:03:16

I want to populate a column with a calculated value on Insert but can't find any example.

The following is not being accepted:

CREATE TRIGGER [SetOrdinalValue] ON [dbo].[ActionList]
FOR INSERT
AS
declare @OrdVal integer

select @OrdVal = Max(OrdNo) from ActionList
where IncID = inserted.IncID

set OrdNo = @OrdVal+1

Basically, the client wants to enter a number of actions into this table and each has to be assigned an ordinal number for the current IncID. Should be simple but it has me stumped.

Dave

Kristen
Test

22859 Posts

Posted - 2005-08-29 : 07:02:58

Hi DaveA, Welcome to SQL Team!

You have to use an UPDATE of the original table, rather than a SET


CREATE TRIGGER [SetOrdinalValue] ON [dbo].[ActionList] 
FOR INSERT
AS
declare @OrdVal integer

select @OrdVal = Max(OrdNo) from ActionList
where IncID = inserted.IncID -- This won't work though, it needs a JOIN

UPDATE ActionList
SET OrdNo = @OrdVal+1
where IncID = inserted.IncID

However, this isn't really going to work either because a trigger needs to be able to operate on a SET of records, not just one - so if you did

INSERT INTO ActionList SELECT * FROM MyTempTable

the trigger would only be called once, and "inserted" would contain all the rows from MyTempTable, so you probably need to do something like:


CREATE TRIGGER [SetOrdinalValue] ON [dbo].[ActionList] 
FOR INSERT
AS
declare @OrdVal integer

select @OrdVal = Max(OrdNo) from ActionList
where IncID = inserted.IncID

UPDATE U
SET @OrdVal = OrdNo = @OrdVal+1
FROM dbo.ActionList AS U
     JOIN inserted as I
         ON I.IncID = U.IncID

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-29 : 10:20:01

@variable = column = expression
dang, I always forget about that,

.

Just so you know, this type of solution can have some concurrency drawbacks. It can cause excessive blocking and possible deadlocks if inserts are performed frequently by different users.

quote:
Basically, the client wants to enter a number of actions into this table and each has to be assigned an ordinal number for the current IncID.

Are you sure the number needs to be sequencial and start with one for each IncID? An identity column wouldn't cause concurrency issues and would satisfy these requirements.

EDIT:
one tweek on kristen's (for when its a new IncID):


UPDATE U
SET @OrdVal = OrdNo = isNull(@OrdVal,0)+1
FROM dbo.ActionList AS U
     JOIN inserted as I
         ON I.IncID = U.IncID

Be One with the Optimizer
TG

DaveA
Starting Member

6 Posts

Posted - 2005-08-29 : 11:38:01

Hi Kristen

Thanks for your pointers. I finally got this to work:

CREATE TRIGGER [SetOrdinalValue] ON [dbo].[ActionList]
FOR INSERT
AS
declare @OrdVal smallint

select @OrdVal = Coalesce(Max(L.OrdNo), 0) from ActionList as L
where L.IncID = (select IncID from inserted)

UPDATE U
SET OrdNo = @OrdVal+1
FROM dbo.ActionList AS U
JOIN inserted as I
ON (U.IncID = I.IncID and U.OrdNo is null)

Although it works, I'm still a bit a bit wobbly about it. I did have the Update statement as

UPDATE U
SET OrdNo = @OrdVal+1
FROM dbo.ActionList AS U
JOIN inserted as I
ON U.IncID = I.IncID
where U.OrdNo is null

but this was updating all the rows sharing the same IncID, regardless of whether OrdNo was null.

Kristen
Test

22859 Posts

Posted - 2005-08-29 : 12:13:46

SET @OrdVal = OrdNo = isNull(@OrdVal,0)+1

Thanks TG, good catch!

DaveA:

Well, I have no idea what the logic involved in your Business Rules is here, but a couple of obervations:

select @OrdVal = Coalesce(Max(L.OrdNo), 0) from ActionList as L
where L.IncID = (select IncID from inserted)

What is the intended action when you insert more than 1 row in a single INSERT statement and more then one IncID is present in the Inserted Record Set?

ON (U.IncID = I.IncID and U.OrdNo is null)

So if you insert a new record with a non-NULL value in OrdNo it won't be changed?

UPDATE U
SET OrdNo = @OrdVal+1

If you insert multiple rows they will all get the same value? (Previous MAX value plus 1)?

(If any of those bother you

then if you post an English description of what the Busioness Rules are someone here will be able to suggest some suitable coding suggestions)

Kristen

DaveA
Starting Member

6 Posts

Posted - 2005-08-30 : 05:06:39

Hi Kirsten

The business logic is extremely simple. We are recording a list of actions to be taken following an incident. The user will enter these 1 at a time - there are never multiple inserts. Each action must be allocated a sequence number within the incident. Over time, there may be multiple incidents, each with their own sequence of actions.

The lines

select @OrdVal = Coalesce(Max(L.OrdNo), 0) from ActionList as L
where L.IncID = (select IncID from inserted)

were to find the highest sequence number already used for the incident. Risky in a highly volatile database but incidents are rare so it is acceptable here.

I had to use

ON (U.IncID = I.IncID and U.OrdNo is null)

to suppress the update of ALL the rows for the current incident. It seems that using the select max() statement caused all the rows for the incident to be added to inserted.

I am coming from a background where a trigger fires for a single row. It is a trivial task to populate a column in the row. It's a bit of a culture shock dealing with this new logic.

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 09:09:11

"there are never multiple inserts"

In that case I'd put a RAISERROR in there "just in case"

IF (SELECT COUNT(*) FROM inserted) > 0 .... Blow up!

for some future event when it happens that someone does a bulk import wihtout realising that the trigger has been designed for a single row update

"Risky in a highly volatile database"

I don't think its risky. The trigger is within a transaction block (either one you are enforcing, or an implied on to make it atomic with the INSERT - at least that's my understanding!), so other than blocking concurrency (which I actually think is unlikely to cause significant performance issues in this situation) I reckon its a reasonable plan.

"It seems that using the select max() statement caused all the rows for the incident to be added to inserted"

Nope, that's not the case. A select cannot cause a trigger to fire.

Are you sure tha U.IncID uniquely describes the record?

"U.OrdNo is null" looks a little dangerous to me as a "fix" to the problem you have described, I'd put a:

SELECT COUNT(*) AS [InsertedRowCount]
FROM inserted

SELECT *
FROM dbo.ActionList AS U
JOIN inserted as I
ON U.IncID = I.IncID

to see what rows are actually being considered for updating, in order to trace down the bug.

Kristen

DaveA
Starting Member

6 Posts

Posted - 2005-08-30 : 11:04:26

Hi Karsten

I have replaced IncID with the correct primary key column and everything works as intended.

Thanks for your patience

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 11:52:00

"I have replaced IncID with the correct primary key column"

Excellent!

"Thanks for your patience"

No problem!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources