CODEWITHSUNDEEP
sql-servert-sqltriggersinsert
Galma88
Galma88

Reputation: 2546

TSQL trigger instead of insert

I need to replace, after an insert in a certain table, one of the column value if it match with a banned value.

example:

INSERT INTO [dbo].[TEST_A]
           ([COL_A]
           ,[COL_B])
     VALUES
           ('TEST'
           ,'TEST1')

TEST1 is a banned value and I want to replace it in 'BANNED'.

This is the trigger I wrote but it seems to working properly well:

CREATE TRIGGER [dbo].[TEST_NAME_INS] ON [dbo].[TEST_A]
 INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO TEST_A
    (COL_A
    ,COL_B)
    SELECT 
    COL_A
   ,REPLACE(COL_B, 'TEST1', 'BANNED')
   FROM inserted
   WHERE INSERTED.COL_B IN ('TEST1')

The error is that if I insert a different value in COL_B no rows are inserted.

Can you give me a solution?

thanks in advance

Upvotes: 2

Views: 3130

Answers (2)

HLGEM
HLGEM

Reputation: 96552

If you have more values than you want to put in a case statement, you can try using a table to store the banned words and the replacement for them. Then join to that in the insert, something like:

CREATE TRIGGER [dbo].[TEST_NAME_INS] ON [dbo].[TEST_A]
 INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO TEST_A
    (COL_A
    ,COL_B)
    SELECT 
    COL_A
   ,CASE WHEN b.banned is null then i.col_b
         ELSE b.replacement
    END
   FROM inserted i
   LEFT JOIN Banned b on i.col_B = b.banned

Upvotes: 5

Vicky_Burnwal
Vicky_Burnwal

Reputation: 981

You need to modify your trigger.

CREATE TRIGGER [dbo].[TEST_NAME_INS] ON [dbo].[TEST_A]
 INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO TEST_A
    (COL_A
    ,COL_B)
    SELECT 
    COL_A
   ,CASE WHEN COL_B ='TEST1' THEN 'BANNED'
         WHEN COL_B ='TEST12' THEN 'BANNED34'
         ELSE COL_B END

   FROM inserted

Upvotes: 3

Related Questions