Trigger that updates the table of that trigger

Question

Trigger that updates the table of that trigger

zenrigar

Ten Centuries

Points: 1350
More actions
May 19, 2009 at 10:56 am

#209877

Hi all :-D.
I have this lil problem.
Suppose i have Table A and Trigger A on it.
Table A just have ie. ITEM and PRICE.
When a new item is inserted in table A im supossed to calculate the PRICE field based on the ITEM inserted using other tables and then update the PRICE field with the calculated value.
But i cant get it updated in the trigger.
Im having that problem since it seems that the trigger locks the table A (i think is that).
So the practical question is: ¿Is there a way to tell the trigger to update the PRICE field of the item that has just been inserted? ¿ Should i work with some locks?
I have been looking and i've seen some ppl that uses #temporary tables or something external.
But i think it can be done inside the trigger.
Can u guys help on that?
Thanks in advance 😀

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

As usual, we'd need more info..your current trigger you tried, the CREATE TABLE definition of the table, the Actual "rule" you want to use to calculate the correct price.

here's a very simple example; you can see i'm joining to a table outside of the trigger

CREATE TRIGGER TR_WHATEVER

ON WHATEVER

FOR INSERT

AS

UPDATE WHATEVER

SET PRICE= OTHERTABLE.COST * 1.25

FROM INSERTED

INNER JOIN OTHERTABLE ON INSERTED.PRODUCTID = OTHERTABLE.PRODUCTID

WHERE WHATEVER.WHATEVERID=INSERTED.WHATEVERID

[/code]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

zenrigar Ten Centuries Points: 1350 More actions · Answer 2

Thanks for your response lowell.

I used the sample u posted in a little rutine and it worked then i adapted my trigger at the same form.

and worked too. But in my front end application a ERP sistem, it doesnt work, guess the damn ERP is doing

something between my trigger that is locking me up.

Ill need to check that because now the trigger alone in sql works, and the problem presists in the ERP.

Hope the profiler helps me to identify the problem T_T

Thanks for your help. 🙂

RP_DBA SSCertifiable Points: 5116 More actions · Answer 3

Would it make a difference if you reworked Lowell's trigger into an INSTEAD OF rather than a FOR/AFTER?

_____________________________________________________________________
- Nate

@nate_hughes

Lowell SSC Guru Points: 323518 More actions · Answer 4

glad the example got you pointed in the right direction; did you run a trace to see exactly what the ERP is doing?

triggers are rarely affected by anything outside of itself, so it may be something else

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!