insert on trigger not working

[junkmail]

Can someone tell me why the following does not work? I do not get any errors but the insert never takes place. This trigger is done on a view. Thanks in advance for the help.


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [dbo].[fs_entertomailshop] ON [dbo].[fs_vw_entertomailshop] instead of insert AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @refnum char(10);
declare @projname char(100);
declare @u1comments char(2000)


set @refnum = (select jobnumber from inserted)
set @projname = (select jobdescription from inserted)
set @u1comments = (select ppinstructions from inserted)
--insert into mailshop2ksql.tblorders (refnum,projname,u1comments)
-- values(@refnum,@projname,@u1comments)
insert into mailshop2ksql..tblorders (refnum,projname,u1comments)
values('99999s','yryryry','gdggdg')


END

 

[bborissov]

One rule:
NEVER, NEVER program a trigger to handle just one record.
Remember, the trigger fires after the whole job is done. And even your application update/insert/delete one records at a time there could be maintenance tasks done by DBA that could handle more than one record.

So try this:

ALTER TRIGGER [dbo].[fs_entertomailshop] ON [dbo].[fs_vw_entertomailshop]
      INSTEAD OF INSERT AS
BEGIN
     SET NOCOUNT ON;
     INSERT INTO mailshop2ksql.tblorders (refnum,projname,u1comments)
     SELECT jobnumber, jobdescription , ppinstructions 
            FROM Inserted
END

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[junkmail]

Still did not seem to work. I have a view that is formed from two different tables in the same database based on a specific criteria and I want to insert some of the information from the view into another table in a different database when a record is added to the view. The view seems to have the new records added but the insert into the other table in the second database never occurs. Should I be using an "update" instead of an "insert" as I am not directly adding records to the view?

 

[Qik3Coder]

junk,
I get the feeling we need to levelset here.

A view is a presentation layer of more complex data and logic.
It's basically a stored select statement that cannot be as complex as a stored procedure, but allows you to join onto for further queries.

There is no separate "copy" of the data for the view. When you add data to the source tables it becomes available to the view to show. In reverse, you can update a view and it will actually alter the data in the source tables. Note that updating 1 row in a view may cause multiple rows to be updated in the source tables.

You probably want an insert trigger on the source table.

HTH,
Lodlaiden

I'll answer your question, not solve your problem

 

[junkmail]

Thanks for the advice.