ALTER TRIGGER [dbo].[upTest] 
   ON  [dbo].[opportunities_cstm]
   AFTER UPDATE, INSERT
AS 
BEGIN

	SET NOCOUNT ON;

DECLARE @TheMax integer
SELECT @TheMax = MAX (quote_id_c) + 1 FROM opportunities_cstm

	UPDATE O
		SET O.quote_id_c=@TheMax
	FROM 
		opportunities_cstm O 
	join 
		inserted i
	on 
		O.id_c = i.id_c 
WHERE O.quote_id_c IS NULL
END

ALTER TRIGGER [dbo].[upTest] 
   ON  [dbo].[opportunities_cstm]
   AFTER UPDATE, INSERT
AS 
BEGIN

	SET NOCOUNT ON;

DECLARE @TheMax integer
SELECT @TheMax = MAX (quote_id_c) + 1 FROM opportunities_cstm

	UPDATE O
		SET O.quote_id_c=@TheMax
	FROM 
		opportunities_cstm O 
	join 
		inserted i
	on 
		O.id_c = i.id_c 
WHERE O.quote_id_c IS NULL

UPDATE opportunities SET name='tryme2'
WHERE id = 'd6204c18-c33d-5f61-362e-48e4d51a1628'

END

quote:

---

Originally posted by visakh16

you mean only last update or both updates? even with hardcoded id?

---

quote:

---

Originally posted by jhermiz

quote:

---

Originally posted by visakh16

you mean only last update or both updates? even with hardcoded id?

---

The first update works fine, the second one does not execute in a trigger. If I place it in a seperate SSMS query window and execute it it runs fine...

No clue why it isnt running. Is there anything wrong with my syntax?

Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

---

quote:

---

Originally posted by Peso

Have you tried SQL Profiler to see exactly what statements that are run?
Can you see the second update in the trigger?

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by Peso

Is there another trigger on opportunities table?
If so, do you have cascading triggers disabled?

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by jhermiz

quote:

---

Originally posted by Peso

Have you tried SQL Profiler to see exactly what statements that are run?
Can you see the second update in the trigger?

---

E 12°55'05.63"
N 56°04'39.26"

---

Yes I have tried profiler and it does display both update statements.
If I place that update in a "New Query" window it works fine...


Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

---

quote:

---

Originally posted by X002548

Why null instead of the key?

Heck the whole table could have null in that column, not just the inserted value...and what's with the hard coded id...you sure it's there for the second update?

Never mind the transactional timing thing for the MAX value

Put out some sample data, ddl and expect results



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by jhermiz

quote:

---

Originally posted by Peso

Have you tried SQL Profiler to see exactly what statements that are run?
Can you see the second update in the trigger?

---

E 12°55'05.63"
N 56°04'39.26"

---

Yes I have tried profiler and it does display both update statements.
If I place that update in a "New Query" window it works fine...


Weblog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

---

and you're sure no other triggers exists on oppurtunities table for UPDATE action -either AFTER or INSTEAD OF?

---