ANSI SQL Trigger on INSERT & UPDATE

Question

I'm looking to write an ANSI-compliant SQL Trigger, and believe I am 90% of the way there but am missing a few pieces. I also have some questions/concerns about its performance (both as a SQL statement and as a trigger).

I have a table called widgets:

widgets
widget_id   |   widget_name   |   widget_type_id   |   widget_is_fizz
========================================================================
1               Apple               1                   0
2               Kiwi                2                   0
3               Pear                3                   1
...             ...                 ...                 ...
2634            Banana              28                  0

I need the trigger to fire anytime an INSERT or UPDATE happen on this table:

• For INSERTs where the widget_type_id is 17 and widget_is_fizz is 1 (true); or
• For UPDATEs (for the same reason as above)

When a "fizz widget" with a type ID of 17 is inserted or updated on this table, I want to change its widget_name to "UNKNOWN".

So far, my best attempt:

CREATE TRIGGER widget_name_trigger
    ON
        widgets
    FOR
        INSERT
    AS
        UPDATE
            widgets
        SET
            widget_name = 'UNKNOWN'
        WHERE
            widget_type_id = 17
            AND
            widget_is_fizz = 1

Still can't figure out how to get it to do the same for UPDATEs as it does for INSERTs. Also not sure if it's even syntatically correct and ANSI-compliant.

Also, in terms of performance, rumor has it that our DBA hates triggers and will likely put up a fight when I take this before him for a code review. Are triggers inherently expensive? If so, why, and is there anything I can do here to mitigate that slowness? Thanks in advance.

Answer 1

You can use the same trigger for both insert and update. There is no need to update the whole table, just the inserted/updated row.

CREATE TRIGGER widget_name_trigger
    ON
        widgets
    FOR
        INSERT, UPDATE
    AS
        UPDATE
            widgets
        SET
            widget_name = 'UNKNOWN'
        WHERE
            widget_type_id = 17
            AND
            widget_is_fizz = 1
            AND
            widget_id=inserted.widget_id

In terms of performance: As long as you don't reference other tables the overhead is very low.
From the Sybase manual:

In terms of performance, trigger overhead is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which may be either in memory or on the database device.
The deleted and inserted trigger test tables are always in active memory. The location of other tables referenced by the trigger determines the amount of time the operation takes.