MS SQL 2005 Insert Trigger Failing

Hi.
Im not too familiar with SQL outside the basic select/insert/delete etc however I have the need to use a trigger to automatically insert the values from a view (Quote Details) into a table when a second table (sales orders) is updated.

I created my new table to be an copy of the view using;
----------------------------------------------------------
Select *
from QUOTE_VIEW
into _MP_TEST_TABLE
where 1=0
----------------------------------------------------------

then i tested that it would transfer the data manually using;
----------------------------------------------------------
insert into _MP_TEST_TABLE select * from QUOTE_VIEW where QUOTEID = "<the quote id is used>"
----------------------------------------------------------
and this inserted the data no problem.

So I create a trigger on the sales table as such;

----------------------------------------------------------
USE [Grp_MSCRM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[t_transfer_qo_to_temp]
ON [dbo].[SalesOrderBase]
FOR INSERT, UPDATE
AS
declare @quoteid varchar(100)
Select @quoteid = (select QuoteId from INSERTED)

insert into dbo_MP_TEST_TABLE select * from QUOTE_VIEW where new_nameid=@quoteid
----------------------------------------------------------

When I add a sales order the record is inserted to the sales order table but it does not fire the trigger.

I have also tried adding an 'ID" field to the test table maing it an identity column and primary key - still not working.
I have tested the trigger by hard coding the quoteid variable into the insert statement - but that still doesnt work (unless executed manually) which makes me think the problem is with the tigger not 'firing' on the insert.

Does anyone have any ideas or things I can try?

Thanks in advance.

Matt
LVL 1
mpeacockbcAsked:
Who is Participating?
 
mpeacockbcAuthor Commented:
I beleive the root problem was being caused by the view that I was trying to extract the data from as I had a similar issue of not having any data result from my Insight Scribe Adapter.
When I used a standard view, not a crm-filtered view, the data was available.

Thank you all for your input - however it looks like my issue was not trigger related.
0
 
momi_sabagCommented:
try to create the trigger like

ALTER TRIGGER [dbo].[t_transfer_qo_to_temp]
ON [dbo].[SalesOrderBase]
FOR INSERT, UPDATE
AS
insert into dbo_MP_TEST_TABLE
select * from QUOTE_VIEW join inserted
on new_nameid=quoteid
0
 
mohan_sekarCommented:
change the select statement like this - Select @quoteid = QuoteId from INSERTED
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
even better:
GO
ALTER TRIGGER [dbo].[t_transfer_qo_to_temp]
ON [dbo].[SalesOrderBase] 
FOR INSERT, UPDATE
AS 
insert into dbo_MP_TEST_TABLE 
select * from QUOTE_VIEW where new_nameid in ( select QuoteId from INSERTED where quoteid is not null )

Open in new window

0
 
mpeacockbcAuthor Commented:
Thank you all for the suggestions; unfortunately, none of them worked.
I also tested by replacing the variables with static values and although it executed manually, it did not work when the sales record was inserted.

I have confirmed that the trigger is on the same table that is receiving the sales order inserts.

Its almost like the trigger is not getting 'triggered'. Could there be a 'Turn Triggers Off' checkbox ;-)
0
 
mohan_sekarCommented:
Try this - ENABLE Trigger dbo.t_transfer_qo_to_temp ON dbo.SalesOrderBase
GO
0
 
mpeacockbcAuthor Commented:
sorry mohan_sekar - no change.

I have made a very simple one column test table, and the trigger does write the quoteid into that table. so now im thinking that there must be something wrong with my other table construction or the way it is inserting into that table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.