Trigger for changing data after insert in SQL Server

Question

This is my first question here. I'm an absolute beginner to DML.

The aim is to implement a hierarchy for a later dropdown box in the web ui. The original code is for oracle and i try translate it to SQL Server. In Oracle they solved it with "before insert" and "for each row".

Here is my code so far:

CREATE TRIGGER dbo.test
ON  dbo.TOPOLOGY
AFTER INSERT
AS
BEGIN
    IF @@ROWCOUNT = 0
        RETURN

    SET NOCOUNT ON

    IF INSERTED.HIERARCHY_LEVEL = 0
    BEGIN
        SET INSERTED.L0 = INSERTED.TOPOLOGY_ID
        SET INSERTED.PARENT = NULL
    END
    ELSE
       IF INSERTED.HIERARCHY_LEVEL = 1
       BEGIN
           SET INSERTED.L1 = INSERTED.TOPOLOGY_ID
           SET INSERTED.PARENT = INSERTED.L0
       END
       ELSE
           IF INSERTED.HIERARCHY_LEVEL = 2
           BEGIN
               SET INSERTED.L2 = INSERTED.TOPOLOGY_ID
               SET INSERTED.PARENT = INSERTED.L1
           END
           ELSE
               IF INSERTED.HIERARCHY_LEVEL = 3
               BEGIN
                   SET INSERTED.L3 = INSERTED.TOPOLOGY_ID
                   SET INSERTED.PARENT = INSERTED.L2
               END
               ELSE
                   IF INSERTED.HIERARCHY_LEVEL = 4
                   BEGIN
                       SET INSERTED.L4 = INSERTED.TOPOLOGY_ID
                       SET INSERTED.PARENT = INSERTED.L3
                   END
END

The database drops the error message: syntax error near '.' for all rows containing INSERTED.Lx.

I try to figure out why it doesn't work for hours now...

Where is/are my mistake/s?

Best regards

Tom

In SQL Server, inserted is a table that will contain 0, 1 or multiple rows. You need to write a query that can work with all of the rows in that table. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Jan 30, 2015 at 11:58
yes, you're right. i need somehting which equals the "for each row" in oracle and already found several solutions on the web. but for the moment i need to get it running for a single row first...small steps but big challenge for a newbie. i think king.code's solution will do the trick, i'm already testing. — Tom
– Tom, Commented Jan 30, 2015 at 12:25
There's no for each row in SQL Server - it's up to you to write your trigger in such a way that it can deal with multiple rows being inserted at once. — marc_s
– marc_s, Commented Jan 30, 2015 at 12:54

dario · Accepted Answer · 2015-01-30 11:58:17Z

You have to update your table not the INSERTED table. Try this approach:

UPDATE T
SET T.L0     = CASE T.HIERARCHY_LEVEL WHEN 0 THEN T.TOPOLOGY_ID ELSE T.L0       END
   ,T.L1     = CASE T.HIERARCHY_LEVEL WHEN 1 THEN T.TOPOLOGY_ID ELSE T.L1       END
   ,T.L2     = CASE T.HIERARCHY_LEVEL WHEN 2 THEN T.TOPOLOGY_ID ELSE T.L2       END
   ,T.L3     = CASE T.HIERARCHY_LEVEL WHEN 3 THEN T.TOPOLOGY_ID ELSE T.L3       END
   ,T.L4     = CASE T.HIERARCHY_LEVEL WHEN 4 THEN T.TOPOLOGY_ID ELSE T.L4       END
   ,T.PARENT = CASE T.HIERARCHY_LEVEL WHEN 0 THEN NULL                  
                                      WHEN 1 THEN T.L0          
                                      WHEN 2 THEN T.L1          
                                      WHEN 3 THEN T.L2
                                      WHEN 4 THEN T.L2          ELSE T.PARENT   END
FROM TOPOLOGY AS T
INNER JOIN INSERTED AS I
    ON T.KEY = I.KEY -- Your Primary Key

Since INSERTED is a table, you can't use IF like you did. You have to join your table with the INSERTED table to update just the inserted rows.

Collectives™ on Stack Overflow

Trigger for changing data after insert in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related