Trigger on View

[vbdbcoder]

I have a database view which pulls records from a table in an external database. How do I detect record change in the view? I have created an "Instead Of" trigger on the view and it never get fired. I can't create the trigger on the source table. Below is the code, but never runs. What to change to detect record change?

ALTER TRIGGER [dbo].[trigger_A]
ON [dbo].[view_MORTST_ICILOC]
instead of INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @ITEM as varchar(24);
declare @QtyOnHand as decimal(19,4);

select @ITEM = i.ITEMNO from inserted i;
select @QtyOnHand = i.QTYONHAND from inserted i;

insert into ITEM_CHANGE (ITEM, QTYONHAND, DT) values (@ITEM, @QtyOnHand, getdate())

END

go

Thanks in advance.

 

[djj55]

Why not trigger the table the view is based on?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[djj55]

Sorry skipped the sentence.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[born2program]

How are you testing the trigger?

 

[vbdbcoder]

I update or insert a record in the source table in the external database, and expect to see that record populated in ITEM_CHANGE table.

If a trigger is created in a table, it can capture the change from "inserted". However, I am not allowed to create it in the source table, for some non-technical reasons (Duh...). I can't think of another way other than creating a view to try to capture the change from the view.

 

[born2program]

To use the trigger on a view you would have to insert the row to the view. Which I think doesn't help you much since it doesn't insert it into the source table also.

 

[born2program]

How are rows being inserted, updated etc in your source table (ie. through an application, writing insert statements, etc)?

 

[vbdbcoder]

The insert and update to the source table is done through an application.

 

[born2program]

The only other options you would have would be to: 1.) insert/update the records in the ITEM_CHANGE table at the time you insert/update to the source table in the application (if you are the programmer for the app). OR 2.)Create a stored procedure that would run nightly or hourly that would check the changes made in the source table and update/insert to the ITEM_CHANGE table as needed.

 

[markros]

Well, I believe you also need to update/insert information into the view itself?

ALTER TRIGGER [dbo].[trigger_A]
   ON  [dbo].[view_MORTST_ICILOC] 
   instead of INSERT,UPDATE
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

insert into ITEM_CHANGE (ITEM, QTYONHAND, DT) SELECT ItemNo, QtyOnHand, getdate()) from Inserted

-- Here you need to modify the original view also
END

PluralSight Learning Library