CODEWITHSUNDEEP
sqlsql-servertriggersview
AndroidAL
AndroidAL

Reputation: 1119

SQL Trigger to update multiple columns in a View

I have this trigger that updates the value format of a column, But I would like to make it update on more then one column in a view instead of a table.

The trigger;

CREATE TRIGGER [dbo].[TriigerName]
ON [dbo].[Table]
AFTER INSERT
AS
BEGIN
UPDATE 
    t
SET 
 t.ColName = dbo.FunctionName(i.ColName)
FROM 
    dbo.table t
INNER JOIN 
    inserted i
    ON
    i.PrimaryId = t.PrimaryId
END

I have tried adding a second t.colName = dbo.FunctionName(i.colName) under SET but that did not work. The query ran, but did not update the value in the second column.

How can I modify this trigger to make it run on a view?

Thanks

EDIT 1: I get this error ; View or function 't' is not updatable because the modification affects multiple base tables. and I also changed AFTER INSERT to INSTEAD OF INSERT.

Upvotes: 0

Views: 1935

Answers (2)

BeanFrog
BeanFrog

Reputation: 2315

As user3238101 said, you cannot update 2 different tables with one statement, so you need to make 2 statements. 

CREATE TRIGGER [dbo].[TriigerName]
ON [dbo].[Table]
AFTER INSERT
AS
BEGIN
    UPDATE 
        t
    SET 
        t.ColName = dbo.FunctionName(i.ColName)
    FROM 
        dbo.table t
    INNER JOIN 
        inserted i
        ON
        i.PrimaryId = t.PrimaryId

    UPDATE 
        t
    SET 
        t.ColName2 = dbo.FunctionName2(i.ColName2)
    FROM 
        dbo.table t
    INNER JOIN 
        inserted i
        ON
        i.PrimaryId = t.PrimaryId
END

Upvotes: 1

sandor
sandor

Reputation: 671

I think that your error message is normal. I guess your view is based on multiple tables

Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

Check this link:

http://docs.oracle.com/cd/B10501_01/server.920/a96521/views.htm#391

Upvotes: 1

Related Questions