August 10, 2017 at 8:29 am
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
Fails with
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
August 10, 2017 at 8:35 am
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
August 10, 2017 at 8:37 am
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
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply