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
Kinderly WadeprogrammerAsked:
Who is Participating?
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.

ste5anSenior DeveloperCommented:
You can use need INSTEAD OF triggers.

But in your scenario it seems unnecesary. INSTEAD OF triggers on views reqiure that you only use the views, not the tables. For logging data on tables you normally use normal triggers on the table.

Caveat: your trigger don't work correctly. It will only "log" one single, arbitrary row in a multi-row update. Also *never* use INSERT without column list or the asterisk in a trigger without good reason.

CREATE TRIGGER tr_Products
ON dbo.Products
AFTER INSERT, UPDATE
AS
    SET NOCOUNT ON;

    INSERT INTO product_log
        SELECT *
        FROM INSERTED;
GO

Open in new window

0

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
ste5anSenior DeveloperCommented:
btw, a complete example should look like this:

CREATE TABLE Products
(
    ProductID INT,
    ProductName NVARCHAR(255) NOT NULL,
    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED ( ProductID )
);

CREATE TABLE ProductLog
(
    ProductID INT,
    ProductName NVARCHAR(255) NOT NULL,
    ChangeType CHAR(1) NOT NULL,
    ChangeDateTime DATETIME NOT NULL,
    ChangeUser SYSNAME NOT NULL
);
GO

CREATE CLUSTERED INDEX CIX_ProductLog ON Products ( ProductID, ChangeDate );

ALTER TABLE ProductLog
ADD CONSTRAINT DF_ProductLog_ChangeDate DEFAULT GETDATE() FOR ChangeDate;

ALTER TABLE ProductLog
ADD CONSTRAINT DF_ProductLog_ChangeUser DEFAULT SUSER_SNAME() FOR ChangeUser;
GO

CREATE TRIGGER tr_Product_I ON Products
AFTER INSERT
AS
  SET NOCOUNT ON;

  INSERT INTO ProductLog ( ProductID, ProductName, ChangeType )
    SELECT  ProductID, ProductName, 'I'
    FROM    INSERTED;
GO

CREATE TRIGGER tr_Product_I ON Products
AFTER UPDATE
AS
  SET NOCOUNT ON;

  INSERT INTO ProductLog ( ProductID, ProductName, ChangeType )
    SELECT  ProductID, ProductName, 'U'
    FROM    INSERTED;
GO

CREATE TRIGGER tr_Product_D ON Products
AFTER DELETE
AS
  SET NOCOUNT ON;

  INSERT INTO ProductLog ( ProductID, ProductName, ChangeType )
    SELECT  ProductID, ProductName, 'D'
    FROM    DELETED;
GO

Open in new window


Here you also see why using the column list for the INSERT statement is necessary.

Caveat: Using UNIQUEIDENTIFIER as key column can lead to performance problems, when it is a random UID and it is part of the clustered index. Which is the primary key per default.
0
ste5anSenior DeveloperCommented:
Sample solution.
0
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
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.