Amending an INSERT via INSTEAD OF TRIGGER

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)

Amending an INSERT via INSTEAD OF TRIGGER

Author

Topic

kendo666
Starting Member

12 Posts

Posted - 2005-05-03 : 11:35:43

Hi,

I have an issue where when a row is inserted (via a unmodifiable front-end app), I have a trigger that modifies some of the inserted values (e.g. concat to a varchar field extra info). I'd done this as a FOR INSERT TRIGGER which included an update statement to the inserted row. All fine, except till i now need an update trigger as well (which is wrongly being fired by the insert trigger).
I've been reading about INSTEAD OF TRIGGERs and was wondering what happens. Do I need to get all the values from inserted, alter some of them and then do an INSERT again? If so, will this re-call the INSERT trigger?

Also, is this the most effective way of doing what I require? (the only time I like Oracle with BEFORE INSERT triggers)

Thanks
Ken

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 12:18:44

Are you saying that

FOR INSERT

Is firing on an UPDATE?

I don't think so.

Post the TRIGGER

Brett

8-)

kendo666
Starting Member

12 Posts

Posted - 2005-05-03 : 12:32:47

No,

Sorry, what i'm saying is that the insert trigger modifies the inserted data, so i'm (rightly or wrongly) doing an update to the row in the INSERT trigger. e.g.

CREATE TRIGGER Mytrg ON [MY_TABLE]
FOR INSERT
AS

declare @oldCounter varchar(5),
@intType INT,
@intProj INT,
@intID INT,
@intDisc INT

select @intType = (SELECT MYTYPE FROM Inserted)
select @intProj = (SELECT Proj FROM Inserted)
select @intDisc = (SELECT DISC FROM Inserted)
select @intID = (SELECT SYSTEM_ID FROM Inserted)
select @oldCounter = (SELECT MAX(COUNTER) FROM MY_TABLE WHERE MYTYPE = @intType AND Proj = @intProj
AND DISC = @intDisc)

IF @oldCounter is NULL
BEGIN
SELECT @oldCounter = '00000'
END
SELECT @oldCounter = @oldCounter + 1
SELECT @oldCounter = right('00000'+@oldCounter,5)
UPDATE MY_TABLE SET COUNTER = @oldCounter WHERE SYSTEM_ID = @intID

----
Now I also have an update trigger which is being fired from the update in the trigger (i think)...So i'm looking at doing an INSTEAD OF INSERT TRIGGER to replace the above trigger (and so as not to accidently fire the update trigger)

Is this right (or is the above trigger wrong)?
If i use an INSTEAD OF TRIGGER, do i need to call the insert again but picking up values from the INSERTED table?

Cheers
Ken

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 12:38:36

First, you've got a fundamental problem

inserted may have many rows...so you will only get the last row returned in the result set...you need to think set bassed

Can you tell us what you're trying to accomplish? Looks like you're trying to force presntation issues into the table

Brett

8-)

kendo666
Starting Member

12 Posts

Posted - 2005-05-03 : 12:44:08

Hi,

The insert will be caused by a front-end application which a user has logged into. They fill in a form so i'm assuming that the insert will be one row for the selected user. And yes, you are right that some presentation is being performed to set values or amend values that the front end app (which I can't change) doesn't do. When they next retrieve the form data, extra read-only values would have been set (via the trigger)

Cheers
Ken

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-03 : 15:52:26

Ken,

That still doesn't address the very basic problem.

You have to manage many rows at once!

Brett

8-)

kendo666
Starting Member

12 Posts

Posted - 2005-05-03 : 16:56:39

So,

Even thou NO batch inserts will be done via the front end app, i'm wrong in thinking that an insert trigger will fire on each insert and in the context of a user's session?

:(

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 17:58:25

The fundamental problem that Brett was mentioning:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

You need to fix that regardless if you can guarantee that your app will only do one row at a time. You should never assume that it will be this way.

And yes your insert trigger is causing your update trigger to fire since your insert trigger has an update statement in it. It does indeed sound like you need an instead of trigger.

Tara

kendo666
Starting Member

12 Posts

Posted - 2005-05-03 : 18:11:17

Thanks tara,

good job its not gone into production yet ;)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources