Need help with trigger - 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

General SQL Server Forums

New to SQL Server Programming

Need help with trigger

Author

Topic

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-09-10 : 17:14:10

ALTER TRIGGER [dbo].[UpdateDNNRole_DNN_IMIS] ON [dbo].[Orders]

AFTER insert as
Begin

SET NOCOUNT ON

Update dbo.DNN_IMIS_SECURITY_NAME set dbo.DNN_IMIS_SECURITY_NAME.SECURITY_ROLE='5' where dbo.DNN_IMIS_SECURITY_NAME.ID=(Select dbo.Orders.BT_ID from dbo.Orders
inner join dbo.Order_Lines on dbo.Orders.Order_Number=dbo.Order_lines.Order_Number where
dbo.Order_Lines.Product_code like 'GRANT08%' and dbo.Orders.Order_date =(select max(dbo.ORDERS.ORDER_DATE) from dbo.ORDERS ) )

My query works when i execute separetly..But donno why the trigger is not fired when there is an insert on ORDERS table.

Please help me out

Soumya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-10 : 17:29:40

Why aren't you using the inserted trigger table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

kancharlasoumya
Starting Member

6 Posts

Posted - 2008-09-10 : 17:41:09

Can you post an example with inserted trigger

Soumya

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-10 : 17:45:09

There's an example in my blog: http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

Your code is very convoluted, so it is hard to help you. Could you at least start by formatting it? Why would anyone put the WHERE word at the end of a line? How did you generate this code?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-10 : 17:45:56

Something similar to this

ALTER TRIGGER dbo.UpdateDNNRole_DNN_IMIS ON dbo.Orders
AFTER INSERT
AS

SET NOCOUNT ON

UPDATE		t
SET		t.SECURITY_ROLE = '5' 
FROM		dbo.DNN_IMIS_SECURITY_NAME AS t
INNER JOIN	inserted AS i ON i.BT_ID = t.ID
INNER JOIN	dbo.Order_Lines AS ol ON ol.Order_Number = i.Order_Number
WHERE		ol.Product_code LIKE 'GRANT08%'
		AND i.Order_date = (SELECT MAX(Order_date) FROM dbo.Orders)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources