February 20, 2017 at 5:49 am
Hi,
Had a database migration (SQL 2005 to 2008) about a week ago - don't know if it is even relevant. For a few days after the migration a particular batch that updates values in table TABLE123 was fine, but then one night if failed with:
"Error: 515, Severity: 16, State: 2
Cannot insert the value NULL into column 'Router_Number', table 'dbo.OnlineActivity'; column does not allow nulls. INSERT fails.
Error: 3931, Severity: 16, State: 1
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction."".
The trigger syntax is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[trg_TABLE123] ON [dbo].[TABLE123]
For INSERT, UPDATE
AS
BEGIN
DECLARE @routerno numeric(11,0);
DECLARE @activity int;
DECLARE @status char(1);
SELECT @routerno=i.accountnum1, @status=i.status1 FROM INSERTED i;
BEGIN
IF EXISTS(SELECT * FROM DELETED)
BEGIN
IF @status = 'S'
BEGIN
SAVE TRANSACTION InsertAct5
BEGIN TRY
set @activity = 5
insert into Activity (Activity_Date, Router_Number, Activity_Number)
values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION InsertAct5
BEGIN TRY
SAVE TRANSACTION MessageInsert
Insert into TriggerError_Log(error_date, trigger_name, Router_Number, Activity_Number, error_code, error_message)
values (getdate(), 'TABLE123', @routerno, @activity, CAST(ERROR_NUMBER()as VARCHAR), CAST(ERROR_MESSAGE()as VARCHAR))
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MessageInsert
END CATCH
END CATCH
END
END
ELSE
BEGIN
SAVE TRANSACTION InsertAct1
BEGIN TRY
set @activity = 1
insert into OnlineActivity (Activity_Date, Router_Number, Activity_Number)
values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION InsertAct1
BEGIN TRY
SAVE TRANSACTION MessageInsert
Insert into TriggerError_Log(error_date, trigger_name, Router_Number, Activity_Number, error_code, error_message)
values (getdate(), 'TABLE123', @routerno, @activity, CAST(ERROR_NUMBER()as VARCHAR), CAST(ERROR_MESSAGE()as VARCHAR))
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MessageInsert
END CATCH
END CATCH
END
END
END
GO
To get the batch working the trigger was disabled and everything updated into TABLE123 fine, and the newly updated values of accountnum1 seemed completely normal.
Can anyone tell me why the trigger is reading one of the updates to i.accountnum1 as NULL??? I suspect the select part of the trigger is fundamentally wrong in some way (ie if it gets passed a status that <> 'S' then it will give a NULL for router_number, but I can't see exactly how it should be tweaked :crazy:)
February 20, 2017 at 6:03 am
It is failing at this point:insert into OnlineActivity (Activity_Date, Router_Number, Activity_Number)
values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
The error your showing is saying that the value of @routerno is NULL. Could user's not being passing this value?
Also, I hope that your process never updates or inserts more than 1 row at a time, or the above trigger will not work as expected.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 6:14 am
Hi Thom,
Appreciate the reply. Essentially no, there isn't a NULL getting passed as [TABLE123].[accountnum1] also doesn't allow NULLS and all the values contained in that table after the batch does its updates/inserts are valued account numbers.
Can you possibly explain "Also, I hope that your process never updates or inserts more than 1 row at a time, or the above trigger will not work as expected"?? This should be straightforward, but has me confused.
(I 'obfuscated' the true table and column names above, so hopefully I have not done it incorrectly)
February 20, 2017 at 6:32 am
rarara - Monday, February 20, 2017 6:14 AMCan you possibly explain "Also, I hope that your process never updates or inserts more than 1 row at a time, or the above trigger will not work as expected"?? This should be straightforward, but has me confused.
When you assign your variables form the inserted table it will only take one value for each of the variables. The inserted table could contain more than one updated or inserted value but you will only be doing the insert to table Activity and OnlineActivity for one record.
SELECT @routerno=i.accountnum1, @status=i.status1 FROM INSERTED i;
From this code if you have multiple values you will be missing inserts to Activity and OnlineActivity.
February 20, 2017 at 6:54 am
rarara - Monday, February 20, 2017 6:14 AM...Appreciate the reply. Essentially no, there isn't a NULL getting passed as [TABLE123].[accountnum1] also doesn't allow NULLS and all the values contained in that table after the batch does its updates/inserts are valued account numbers....
The null value check is performed every time the table is touched, not on a per batch level ! Insert into xyz ( col1, col6 ) values (1,'C')
update xyz set col2='abc' where col1 = 1 ;
go
The batch ends with the 'go' in SSMS
The first statement will fail if col2 doesn't allow nulls and has no default value defined (other than null).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2017 at 7:16 am
Let's start off by fixing some of your trigger so that it can handle more than one row, but also making it readable (there's no indentations your post, so telling where one part begins and another ends is impossible).CREATE TRIGGER [dbo].[trg_TABLE123] ON [dbo].[TABLE123]
FOR INSERT, UPDATE
AS
BEGIN
--DECLARE @routerno numeric(11,0); No longer needed
DECLARE @activity int;
DECLARE @status char(1);
--SELECT @routerno=i.accountnum1, @status=i.status1 FROM INSERTED i; --This is not going to work if you have more than 1 line updated/inserted
BEGIN --Any reason for this?
IF EXISTS(SELECT * FROM DELETED) BEGIN
IF @status = 'S' BEGIN
SAVE TRANSACTION InsertAct5
BEGIN TRY
SET @activity = 5;
INSERT INTO Activity (Activity_Date, Router_Number, Activity_Number)
--VALUES (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
SELECT CONVERT(char(8), GETDATE(), 112), i.accountnum1, @activity
FROM inserted i;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION InsertAct5
BEGIN TRY
SAVE TRANSACTION MessageInsert
INSERT INTO TriggerError_Log(error_date, trigger_name, Router_Number, Activity_Number, error_code, error_message)
--VALUES (getdate(), 'TABLE123', @routerno, @activity, CAST(ERROR_NUMBER()as VARCHAR), CAST(ERROR_MESSAGE()as VARCHAR))
SELECT GETDATE(), 'TABLE123', i.accountnum1, @activity, CAST(ERROR_NUMBER() as VARCHAR(10)), CAST(ERROR_MESSAGE()as VARCHAR(200)) --You should declare your VARCHAR lengths
FROM inserted i;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MessageInsert
END CATCH
END CATCH
END
END
ELSE
BEGIN
SAVE TRANSACTION InsertAct1
BEGIN TRY
SET @activity = 1;
INSERT INTO OnlineActivity (Activity_Date, Router_Number, Activity_Number)
SELECT CONVERT(char(8), GETDATE(), 112), i.accountnum1, @activity
FROM inserted i;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION InsertAct1
BEGIN TRY
SAVE TRANSACTION MessageInsert
INSERT INTO TriggerError_Log(error_date, trigger_name, Router_Number, Activity_Number, error_code, error_message)
SELECT GETDATE(), 'TABLE123', i.accountnum1, @activity, CAST(ERROR_NUMBER() as VARCHAR(10)), CAST(ERROR_MESSAGE()as VARCHAR(200)) --You should declare your VARCHAR lengths
FROM inserted i;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION MessageInsert
END CATCH
END CATCH
END
END
END
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
February 20, 2017 at 7:43 am
Hi Johan,
"The null value check is performed every time the table is touched, not on a per batch level ! " - yes, that was known to me already. I am less clear on what you suggest after that. Do you mean that this from my trigger:
insert into Activity (Activity_Date, Router_Number, Activity_Number) values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
will fail? i thought @routerno is picked up from accountnum1 value in the table? apparently the syntax of the thing that runs and created the updates themselves is along the lines of:
UPDATE TABLE123 SET date1, time1, value12, value45 WHERE ( accountnum1 = :load_x.accountnum1 AND
value45 = :load_y.value45)
February 20, 2017 at 7:56 am
rarara - Monday, February 20, 2017 7:43 AMHi Johan,"The null value check is performed every time the table is touched, not on a per batch level ! " - yes, that was known to me already. I am less clear on what you suggest after that. Do you mean that this from my trigger:
insert into Activity (Activity_Date, Router_Number, Activity_Number) values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
will fail? i thought @routerno is picked up from accountnum1 value in the table? apparently the syntax of the thing that runs and created the updates themselves is along the lines of:
UPDATE TABLE123 SET date1, time1, value12, value45 WHERE ( accountnum1 = :load_x.accountnum1 AND
value45 = :load_y.value45)
It will use the value of @routerno and @activity, but one need to ensure those variables aren't NULL !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2017 at 7:57 am
rarara - Monday, February 20, 2017 7:43 AMHi Johan,"The null value check is performed every time the table is touched, not on a per batch level ! " - yes, that was known to me already. I am less clear on what you suggest after that. Do you mean that this from my trigger:
insert into Activity (Activity_Date, Router_Number, Activity_Number) values (CONVERT(char(8), GETDATE(), 112), @routerno, @activity)
will fail? i thought @routerno is picked up from accountnum1 value in the table? apparently the syntax of the thing that runs and created the updates themselves is along the lines of:
UPDATE TABLE123 SET date1, time1, value12, value45 WHERE ( accountnum1 = :load_x.accountnum1 AND
value45 = :load_y.value45)
Have a look at my above post.
Assigning variables to a value from a table, which could contain multiple values is asking for trouble, especially in a trigger.
Take the following for example:CREATE TABLE #SAMPLE ([UID] UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
ID INT IDENTITY(1,1),
[Name] VARCHAR(10));
GO
INSERT INTO #SAMPLE ([Name])
VALUES ('John'),
('Steve'),
('Jane');
GO
SELECT *
FROM #SAMPLE;
DECLARE @ID INT, @Name VARCHAR(10);
SELECT @ID = ID,
@Name = [Name]
FROM #SAMPLE;
SELECT @ID, @Name;
GO
--What if I Add An INDEX
CREATE CLUSTERED INDEX [Cluster_IX] ON #SAMPLE ([UID] ASC)
GO
DECLARE @ID INT, @Name VARCHAR(10);
SELECT @ID = ID,
@Name = [Name]
FROM #SAMPLE;
SELECT @ID, @Name;
GO
DROP TABLE #SAMPLE;
GO
Before running this code, what would you expect the value of @ID and @Name to be? Then run it, was it what you expected? Run it again, was it this time? What about again?
Notice the problems?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply