SQL server trigger concat

Question

I'm trying to write a SQL Server trigger that concats 3 columns, but inserts a string in place of the third column based on a condition.

For example when col 1 is inserted concat col 1, col 2 and col 3. But when col 3 = 'DR' concat Drive or col 3 = 'ST' concat Street etc.

I can write the trigger for all 3 columns, but having trouble with the conditional.

CREATE TRIGGER updateAddress
   ON [ARCHIVENEW].dbo.a11
   AFTER UPDATE
AS BEGIN
  SET NOCOUNT ON;
  IF UPDATE(STRNAME)
    SELECT CASE
      WHEN a11.STRTYPE = 'DR' THEN dbo.a11.STRFNAME = cast(dbo.a11.STRADDLF as varchar(2)) + ' ' + STRNAME + ' ' + 'Drive'
      WHEN a11.STRTYPE = 'RP' THEN dbo.a11.STRFNAME = cast(dbo.a11.STRADDLF as varchar(2)) + ' ' + STRNAME + ' ' + 'Ramp'
      WHEN a11.STRTYPE = 'EX' THEN dbo.a11.STRFNAME = cast(dbo.a11.STRADDLF as varchar(2)) + ' ' + dbo.a11.STRNAME + ' ' + 'Express Way'
      ELSE dbo.a11.STRFNAME = cast(dbo.a11.STRADDLF as varchar(2)) + ' ' + dbo.a11.STRNAME 
END

Sorry it took a few days to get back to this and add the code sample. I'm getting a syntax error near the '=' where I try and set my STRFNAME to the concatenated string. To clarify further, col1 is "1234" col2 is "Main" and col3 is "ST". When a row is inserted I'd like col4 to say "1234 Main Street".

Have you tried using a CASE? Without seeing your code, we can only guess at what you are trying to do. — Joseph Gagliardo, Jun 05 '15 at 03:42
Thank you Joseph, yea I've tried using CASE, but get syntax error. I'm on a phone will post code tomorrow. — Dara O'Beirne, Jun 05 '15 at 03:47
well if you have a syntax error, you just need to carefully look at your code and count your END and commas and such before you post code. — Joseph Gagliardo, Jun 05 '15 at 03:47
Syntax errors in a CASE statement, eh? Be on the lookout for the TWO variants of the syntax for CASE, SQL's error message is misleading. For example, see: http://stackoverflow.com/questions/30618063/using-in-case-statement-causes-syntax-error/30619678#30619678 — Stan, Jun 05 '15 at 04:08
one thing is not understand that if while insert in col1 , how to get col3 value to calculate. — Ajay2707, Jun 05 '15 at 06:08
Sounds like a job for a computed column rather than a trigger. — Damien_The_Unbeliever, Jun 05 '15 at 06:09

score 0 · Answer 1 · answered Jun 05 '15 at 04:20

Sample table:

CREATE TABLE dbo.TestTable(
    Id INT IDENTITY(1,1) PRIMARY KEY
    ColA VARCHAR(50),
    ColB VARCHAR(50),
    ColC VARCHAR(50),
    ColD VARCHAR(50)
);

This trigger concats values of ColA, ColB for ColC and validates ColB value for ColD.

CREATE TRIGGER TestTable_UpdateTwoColumns
   ON  dbo.TestTable
   AFTER INSERT,UPDATE
AS 
BEGIN
   SET NOCOUNT ON;

   IF UPDATE(ColA)
   BEGIN
      UPDATE  dbo.TestTable
      SET     ColC=new.ColA + new.ColB
      FROM    INSERTED new
   END
   IF UPDATE(ColB)
   BEGIN
      UPDATE  dbo.TestTable
      SET ColD=CASE new.ColB WHEN 'DR' THEN 'Drive' WHEN 'ST' THEN   
      'Street' END
      FROM INSERTED new
   END
END

Thanks,

score 0 · Answer 2 · answered Jun 05 '15 at 04:22

You cannot change a table while the INSERT trigger is firing. You can, however, create a trigger before inserting the record.

CREATE TRIGGER emplog_update AFTER UPDATE ON emp FOR EACH ROW INSERT INTO emplog (id, lastnmae, firstname, . . .) select NEW.id,NEW.lastname,NEW.firstname,NEW.gender,NEW.dob,NEW.marital,NEW.SSN,'U',NULL,USER(), concat_ws(',', (case when new.id <> old.id then 'id' end), (case when new.lastname <> old.lastname then 'lastname' end), (case when new.firstname <> old.firstname then 'firstname' end), . . . );

score 0 · Answer 3 · answered Jun 05 '15 at 06:17

If your motto is insert into third column, good to use computed column. Here is an example. But as in my comment on question, I did not understand, how col3 value comes?

For this, I used col4 to get the proper result, also in computed column define, it not take reference of own.

    CREATE TABLE dbo.Products 
    (
        ProductID int IDENTITY (1,1) NOT NULL
      , col1 varchar(50)
      , col2 varchar(50)
      , col3 varchar(50)  
      , col4  AS case col3 when 'DR' then 'Drive' when 'ST' then 'Street' else col1 + col2 end 
     --while computed column define, you can use all other column, except own.
    );

    -- Insert values into the table.
    INSERT INTO dbo.Products (col1, col2,col3)
    VALUES ('a', 'a1','a2'), ('b', 'b1', 'DR'), ('c', 'c1', 'ST'), ('d', 'd1', 'd2');

    -- Display the rows in the table.
    SELECT ProductID, col1, col2, col3, col4
    FROM dbo.Products;

    select * from products

    drop table products

SQL server trigger concat

3 Answers3