Kinderly Wade
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sample solution.