SQL SERVER AFTER INSERT TRIGGER TO INSERT NON-ZERO VALUES

Question

I want to create a insert trigger on a table called Client1 to insert non-zero values into a table called Client2.

For example, when inserting the values below into Client1 table

Client_ID  CATEGORY1  CATEGORY2  CATEGORY3   CATEGORY4 CATEGORY5
 6000          1          0         1            0          0

It will trigger to insert values into Client2 table:

INSERT INTO Client2 (Client_ID, Category_type,Value)
VALUES (6000, CATEGORY1, 1)

INSERT INTO Client2 (Client_ID, Category_type,Value)
VALUES (6000, CATEGORY3, 1)

Can anyone help with it? Thanks.

First, you should probably think about changing your data structure so that you do not have the category columns on this table at all. Instead have a lookup with Id, Client_Id, Category_Id. When your application creates a client have it insert into your Client1 table, and then also do an Insert into your Client_Category_Lookup for each category that applies. If you just want to do a trigger, write the trigger as if the category value is 1, then wrap the whole thing in an IF(CategoryX = 1). You'll need to repeat the IF() INSERT for each category with your current data structure. — user7396598, Mar 26 '18 at 18:14
Here is an example of how to write a trigger. https://stackoverflow.com/questions/1355921/sql-server-2008-help-writing-simple-insert-trigger -- you'll need to also look up how to write an IF statement in SQL if you are unfamiliar. — user7396598, Mar 26 '18 at 18:15
I want to do a trigger because Client1 table is a staging table. Do you know how to avoid to repeat the if() Insert for each category? — Ice, Mar 26 '18 at 18:30

score 1 · Accepted Answer · answered Mar 26 '18 at 18:34

1

Here is one approach, which unpivots your data into a more usable format for inserting then does so:

CREATE TRIGGER yourTrigger ON Client1
AFTER INSERT
AS
BEGIN
INSERT INTO Client2 (Client_ID, Category_Type, Value)
SELECT Client_ID, Category_Type, Value
FROM inserted
CROSS APPLY (VALUES (CATEGORY1,'CATEGORY1'), 
                    (CATEGORY2,'CATEGORY2'), 
                    (CATEGORY3,'CATEGORY3'), 
                    (CATEGORY4,'CATEGORY4'), 
                    (CATEGORY5,'CATEGORY5')) C (Value, Category_Type)
WHERE Value <> 0
END

You mentioned this is transforming data from a staging table.. Consider putting this in a procedure and calling that, instead of having the trigger fire on every insert.

answered Mar 26 '18 at 18:34

Aaron Dietz

10,137
1
14
26

Schema: client1 (Client_ID int, Category1 int, Category2 int, Category3 int, Category4 int, Category5 int) should you change "SELECT Client_ID, Category_Type, Value FROM inserted" in your SQL? Thanks, – Ice Mar 26 '18 at 18:46
I mean inserted table should have the same schema as Client1 table which I sent to you earlier. So I don't understand 'SELECT Client_ID, Category_Type, Value FROM inserted' . Are you converting client1 (Client_ID int, Category1 int, Category2 int, Category3 int, Category4 int, Category5 int) to the schema of Client2 table using unpivot? – Ice Mar 26 '18 at 19:07
@Ice Ahh, then yes exactly. `Category_Type` and `Value` are the output of the unpivot which happens on `inserted`. It saves you from writing a long list of `if`s and probably a loop – Aaron Dietz Mar 26 '18 at 19:18

score 1 · Answer 2 · answered Mar 26 '18 at 19:06

You can try the following query statement.

CREATE TABLE Client1 (ClintId INT, Category1 INT, Category2 INT,Category3 INT,Category4 INT,Category5 INT)

CREATE TABLE Client2(ClientId INT, CategoryType VARCHAR(100),VALUE INT)

GO
ALTER TRIGGER SampleTrigger  
ON Client1  
AFTER INSERT
AS  
    DECLARE @ClientID INT
    DECLARE @Category1 AS INT
    DECLARE @Category2 AS INT
    DECLARE @Category3 AS INT
    DECLARE @Category4 AS INT
    DECLARE @Category5 AS INT

    SELECT @ClientID = ClintId, @Category1= Category1, @Category2= Category2, 
    @Category3= Category3, @Category4 =Category4, @Category5= Category5 FROM inserted

    IF(@Category1<>0)
    BEGIN
        INSERT INTO Client2(ClientId,CategoryType,Value) VALUES(@ClientID,'Category1',@Category1)
    END
    IF(@Category2<>0)
    BEGIN
        INSERT INTO Client2(ClientId,CategoryType,Value) VALUES(@ClientID,'Category2',@Category2)
    END
    IF(@Category3<>0)
    BEGIN
        INSERT INTO Client2(ClientId,CategoryType,Value) VALUES(@ClientID,'Category3',@Category3)
    END
    IF(@Category4<>0)
    BEGIN
        INSERT INTO Client2(ClientId,CategoryType,Value) VALUES(@ClientID,'Category4',@Category4)
    END
    IF(@Category5<>0)
    BEGIN
        INSERT INTO Client2(ClientId,CategoryType,Value) VALUES(@ClientID,'Category5',@Category5)
    END

After inserting the following values,you can see the effect on Client2 table

INSERT INTO Client1(ClintId, Category1, Category2,Category3,Category4,Category5)
VALUES(6000,1,0,1,0,0)

Thanks.

@Ice,If you got really help from this, please mark as a answer. Thanks. — Emdad, Mar 26 '18 at 19:13
@AaronDietz, I have checked again it is working fine for multiple rows. Thanks. — Emdad, Mar 26 '18 at 19:32
Let's say you insert 3 rows at once. The trigger fires once, but it can't set the `@category` variables to 3 different values. With a `SET @category =` it would literally fail with a "subquery returned more then 1 value" error, but with the `SELECT @category =` it will appear to succeed. However, if you look at `client2` table, you will see that it only inserted one clientID's set of rows, whichever is chosen as the top row when the variables are populated. — Aaron Dietz, Mar 26 '18 at 19:43

SQL SERVER AFTER INSERT TRIGGER TO INSERT NON-ZERO VALUES

2 Answers2