coding a trigger to cope with NULL rows

Question

coding a trigger to cope with NULL rows

simon_s

Ten Centuries

Points: 1358
More actions
August 10, 2017 at 8:29 am

#327928

Ok so Im trying to put together some basic auditing on a particular table via trigger
All works well when a row is inserted but when a row of nulls is passed to be inserted it fails dismally ..e.g
insert table1 (id)
values (null)

Fails with

Msg 515, Level 16, State 2, Line 88
Cannot insert the value NULL into column 'ID', table 'database.dbo.table1'; column does not allow nulls. INSERT fails.

Can I add something to the trigger code to get it to ignore NULL inserts (Im using a silly insert with NULL to illustrate the point but they are happening and I need to code the trigger in a mode "defensive" fashion ! )

Trigger code

CREATE TRIGGER [dbo].[table1_INSERT ] ON [dbo].table1 INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON;

DECLARE @CustomerId INT

SELECT @CustomerId = INSERTED.id FROM INSERTED

INSERT INTO table2 (id, [source]) VALUES(@CustomerId, 'inserted') END

Lowell SSC Guru Points: 323518 More actions · Answer 1

that trigger is extremely dangerous, as data will get lost when multiple rows are inserted, and does not handle the INSTEAD OF logic that is required.
SQL triggers must be designed to handle multiple rows...declaring a variable is a no no, since that distills multiple rows into a single value.
the instead of trigger must populate every column in the table1.; what it is doing now is populating table2, and never populating table1.
you probably just need a FOR trigger to populate table2, and leave the normal inserts alone.

CREATE TRIGGER [dbo].[table1_INSERT]
   ON [dbo].table1 
FOR INSERT
AS
BEGIN
   SET NOCOUNT ON;
   INSERT INTO table2 (id, [source])
   SELECT INSERTED.id  ,'inserted' 
   FROM INSERTED


END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

This trigger will not handle the case where multiple rows are inserted in a single batch, so you need to perform some re-engineering.
insert table2(id, source) select i.Id, 'Inserted' from inserted i where i.Id is not null

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin