CODEWITHSUNDEEP
sqlsql-servertriggers
MulfordnSons
MulfordnSons

Reputation: 29

INSTEAD OF INSERT VIEW trigger - insert into table

I have a table test with columns ID, Test.

I have a view, testview, created from this table that mirrors test. The view populates data inserted into test, as expected.

I have another table, testDestination, that I want to insert into when the view is updated.

I have a trigger on testview as follows:

CREATE TRIGGER insertfromviewtest
ON testTriggerView 
INSTEAD OF INSERT 
AS
BEGIN
    INSERT INTO testDestination (ID, Test)
        SELECT ID, Test
        FROM INSERTED
END

This trigger creates successfully. However, when I update the table test and the view updates, testDestination does not update.

Upvotes: 0

Views: 89

Answers (1)

bluestar
bluestar

Reputation: 100

The trigger will only fire when you attempt to directly insert into testview. However, when you insert into test, the view updates automatically since it mirrors test, but the trigger does not fire because no direct INSERT operation is happening on the view. You know, views don't store the data.

To fix this issue, you need to add trigger on test table directly. like this:

CREATE TRIGGER insertfromtest
ON test
AFTER INSERT
AS
BEGIN
    INSERT INTO testDestination (ID, Test)
    SELECT ID, Test FROM INSERTED
END

Upvotes: 0

Related Questions