I have two key problems with triggers in SQL Server 2017. They are:

1. They don't respect database design.
2. NULL value conditions in the trigger fail.

Let me explain:

1. They don't respect database design: Lets say I have tables and a view like below and I am trying to create a trigger which would insert into multiple tables based on what has been inserted into the view. (taken and edited from this post - Instead of trigger to update view with multiple tables)

CREATE TABLE persons
(personid  int, 
 firstname varchar(32) default 'Charles', 
 lastname  varchar(32));

CREATE TABLE employees
(employeeid int, 
 personid   int, 
 title      varchar(32));

CREATE VIEW vwEmployees AS
SELECT p.personid, employeeid, firstname, lastname, title
  FROM employees e JOIN persons p
    ON e.personid = p.personid;

CREATE TRIGGER tgEmployeesInsert ON vwEmployees
INSTEAD OF INSERT AS
BEGIN
  INSERT INTO persons (personid, firstname, lastname)
  SELECT personid, firstname, lastname
    FROM INSERTED

  INSERT INTO employees (employeeid, personid, title)
  SELECT employeeid, personid, title
    FROM INSERTED
END;

INSERT INTO vwEmployees (personid, employeeid, lastname, title)
VALUES(1, 1, 'Doe', 'SQL Developer');

If I use the select statement like below:

select * from persons

This will fetch me results as below:

I am not sure if you noticed above but the default for firstname should have been 'Charles' as mentioned in the create table above.

2. NULL value conditions in the trigger fail.

Here, I used the same method as above for creating an instead of trigger for updating a view with multiple tables. Lets say I have a trigger like below:

CREATE or ALTER TRIGGER oc.trg_Update_Tracker ON oc.BP_Tracker
INSTEAD OF Update AS
BEGIN  
       Begin Try
       SET NOCOUNT ON
       Begin
            if update(Partner_ID) or update(Partner_Name) or update(LEI) 
            Begin
                Update oc.Business_Partner
                SET Partner_ID=ins.Partner_ID, Partner_Name=ins.Partner_Name, LEI=ins.LEI, User_Changed = ins.User_Changed
                from oc.Business_Partner bp, inserted as ins, deleted as del
                where bp.Partner_ID = del.Partner_ID and bp.Partner_Name = del.Partner_Name
            End
.........

Lets say the bp.Partner_Name is null and del.Partner_Name is null. In this case, the update statement fails because the condition bp.Partner_Name = del.Partner_Name fails in the trigger. If the same update statement were to be passed outside the trigger on the table - that works. Also, if I remove the condition bp.Partner_Name = del.Partner_Name, the trigger update would work.

Would really appreciate if someone could explain me what's going on.

CodePudding user response：

I would put this in a comment, but there no way it's fit:

TL;DR: The problem isn't the trigger, it's your understanding that's flawed.

1: They do respect database design

The DEFAULT value was respected, you just told SQL Server to not use it. Let's look at your INSERT statement:

  INSERT INTO persons (personid, firstname, lastname)
  SELECT personid, firstname, lastname
    FROM INSERTED

So you are inserted the values personid, firstname and lastname from the inserted pseudo table into the table persons. These columns have the value 1, NULL, and 'Doe' respectively. You have, in your INSERT statement specifically stated you want to INSERT the value of firstname into the column of the same name, and that value is NULL, so NULL was inserted.

If you wanted the default value, you should have omitted firstname in the INSERT entirely, or stated DEFAULT in the SELECT:

  INSERT INTO persons (personid, lastname)
  SELECT personid, lastname
    FROM INSERTED;

  INSERT INTO persons (personid, firstname, lastname)
  SELECT personid, DEFAULT, lastname
    FROM INSERTED;

A DEFAULT constraint is only used when the column is omitted from the SELECT or the DEFAULT option is passed. NULL is still a value, just an unknown one, and explicitly inserting NULL into a column does not result in the DEFAULT value to be used.

2: "NULL value conditions in the trigger fail"

Firstly, I need to address your query; it's not 1989 any more. It's 2022, and the ANSI-92 explicit join syntax has been around for 30 years and it is long past time you adopted it.

I cannot, however, test this trigger has the DML and DDL is omitted for the objects it references, and it's own DDL is incomplete. As questions should be limited to 1 question though, then fixing that trigger is for a separate new question.