SQL Server Performance

Create Trigger - Help!

Discussion in 'T-SQL Performance Tuning for Developers' started by kdefilip, Jul 24, 2007.

  1. kdefilip Member

    I'm trying to create a trigger that will concatinate two fields upon an insert or update. I've tried this:

    CREATE TRIGGER fieldCat
    ON card
    AFTER INSERT, UPDATE
    AS
    UPDATE Card
    Set data_21 = DATA_20 + CONVERT(char(9), employee_id)

    All I need to happen is that after an insert, that field data_21 holds the combination of fields data_21 and employee_id.

    Any help would be greatly appreciated.

    Error I'm getting now is:
    Maximum stored procedure, function, trigger or view nesting level exceeded(limit 32)

    Thanks.
  2. Adriaan New Member

    You have two problems: you're updating all the rows in the table all the time, and the trigger is caught up in an infinite loop (which bumps into the nesting level limit of 32).

    A simpler option would be to drop the concatenated column, and add a calculated column instead:

    ALTER TABLE Cart DROP DATA_21
    GO

    ALTER TABLE Card
    ADD DATA_21 AS (DATA_20 + CONVERT(CHAR(9), employee_id))
    GO

Share This Page