sql-servert-sqltriggersidentitymaintainability

Maintainable INSTEAD OF trigger with Identity columns

I want to add a trigger to my table to validate the data before allowing insert or update.

For example, something like this:

CREATE TABLE MyHorribleLegacyTable (i int IDENTITY, name char(100),
 column2 char(2), column3 date, column4 int, ... column224 int)
CREATE TRIGGER ValidateLicense
ON MyHorribleLegacyTable
INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    IF UserDefinedFunction(inserted) = 1
        THROW 9283, 'The zinger is not configured to work with the wok', 16
    IF EXISTS (SELECT * FROM inserted WHERE Name IN ('100001', 'None'))
        THROW 9284, 'Invalid Licence Number', 16

    INSERT INTO MyHorribleLegacyTable
        SELECT * FROM inserted
END

Unfortunately, this table has an identity column, so SQL Server throws an error

An explicit value for the identity column in table 'dbo.Licences' can only be specified when a column list is used and IDENTITY_INSERT is ON.

The 'fix' is to change the INSERT statement to explicitly list all the columns (except the identity column). But this is a maintenance nightmare.

What happens when another developer adds a column (with a constraint) into the table and does not check every trigger on the table. Any code that attempts to insert a non-default value into this new column does not work as the new column is not explicitly listed in the INSERT statement.

Is there a better way to do the above that is maintainable?

A constraint is not possible because of the complexity.

Assuming well written code elsewhere in the system, adding the new column to the table does not break anything except the trigger code.

Solution

  • The 'fix' is to change the INSERT statement to explicitly list all the columns

    You should be doing that anyway. It's not a maintenance nightmare at all; it's just good practice.

    If you don't want to type out the column list you can use SSMS and drag the "columns" folder from the object explorer to the query window. Or generate it with a query like

    select object_name(c.object_id) tn, string_agg(quotename(c.name),', ')  cols
from sys.columns c
group by c.object_id