Link to home
Start Free TrialLog in
Avatar of Kinderly Wade
Kinderly WadeFlag for United States of America

asked on

insert or update data table from a view with trigger

Hi Experts!

I am wondering if I can create a trigger in SQL SERVER 2016 that will use a view instead of the table to perform a log of update/insert action?

Here is my scenario:

1. I have a view called Products (this view contains 2 table joins in order to generate this view)

2. I have created a table named product_log. It has the exact same structure as the Product view.

3. I created a trigger named insUpdProd_trig with these settings:
   a. Trigger Type: View
   b. Table Name Products
   c. Fire: INSTEAD OF (this is the only option)
  d. On Event: I checked Insert and Update
  f. Def Type: SQL Statement
 g. Execute as Caller (this is under Advanced Tab)
 h code is:
   declare @productID uniqueidentifier
 
   select @ProductID = productID from inserted

  Insert into product_log SELECT * FROM Products WHERE productID = @ProductID


If I use this trigger on the table version I can get the trigger to work when user update the product or insert a new product from Product table (I have the Trigger type set to Table and Table View to Product and trigger will insert the row into product_tbl_log. When I checked the product_log there are no rows been inserted.

QUESTION:

1. can I track the product update and insertion from a view?

2. If I can do so then what do I need to change in my trigger code in order for this to happen as when user makes changes that it will insert a row into the product_log table?

Thanks
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sample solution.