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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
mohan_sekarCommented:
change the select statement like this - Select @quoteid = QuoteId from INSERTED
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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 ;-)
mohan_sekarCommented:
Try this - ENABLE Trigger dbo.t_transfer_qo_to_temp ON dbo.SalesOrderBase
GO
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.