March 18, 2009 at 6:41 am
Hi,
I have a SP which takes the data from one staging table and kept it into production table.
staging table: stg_tempemp
original table: Employee
please find the below code I attached and it was worked well till last week where another team has added an Insert trigger to the orignal table Employee. Now I am getting the error__
Cannot insert the value NULL into column 'empID', table 'Dept.dbo.department'; column does not allow nulls. INSERT fails.
The insert query I have is___
INSERT INTO [Emp].[dbo].[Employee]
(
[EmpID],
[EmpName],
[EmpAddr],
[Empexp],
[EmpCtNo],
[EmpLoc]
)
SELECT [t_EmpID],
[t_EmpName],
[t_EmpAddr],
[t_Empexp],
[t_EmpCtNo],
[t_Emploc]
FROM [Emp].[dbo].[stg_tempEmp]
WHERE ISNULL(newRecFlag, '0') <> '1'
AND ISNULL(LTRIM(RTRIM(empID)),
'') <> ''
AND recNum = @rcount
The above query is to filter the duuplicates. if staging table flag is not equal to 1 then it will take. But in this scenario only one record there in staging table and it;s flag is 1.
trigger will take the EmpID, EmpName and insert into Department table. Here EmpID should not be null in table. Here no record is selecting from the above sql stmt.
Any help would be really appreciated.
Thank You
March 18, 2009 at 6:56 am
Hello Venki
Seems that the trigger tries to write incorrect data into table "Dept.dbo.department". Can you post the trigger source?
Greets
Flo
March 18, 2009 at 7:12 am
Florian Reischl (3/18/2009)
Hello VenkiSeems that the trigger tries to write incorrect data into table "Dept.dbo.department". Can you post the trigger source?
Greets
Flo
Nothing, trigger simply takes the data into a variable from INSERTED table and inserting into Department table.
CREATE TRIGGER [tr_Insertempdept]
-- The table name to affect or
-- writing the trigger on
ON [dbo].[Employee]
-- The type of trigger to implement
FOR INSERT
AS
-- Declaring variabes to hold values
-- from Virtual table
DECLARE @tr_empID varchar(50)
DECLARE @tr_empName Varchar(50)
-- Setting values of variables to the values
-- from virtual table.
-- By default name of virtual table is Inserted
SELECT @tr_empID = (SELECT empID FROM Inserted)
SELECT @tr_empName = (SELECT empName FROM Inserted)
-- Using the variables to insert data into
-- the Department Table in Dept
INSERT Dept.dbo.Department values (@tr_empID,@tr_empName)
Thank You
March 18, 2009 at 7:29 am
Two things I think you need to do
a) Get the trigger changed to properly handle multiple rows in one statement. Lots of examples are around..
b) Your code is presumably processing a row with a null empid, which the trigger in turn tries to insert in to the department table. So either your code needs to get rid of the null empid, or the trigger needs top cater for it, or it should be nullable in the department table. Which is right depends on what you are doing...
Mike John
March 18, 2009 at 7:51 am
Mike John (3/18/2009)
Two things I think you need to doa) Get the trigger changed to properly handle multiple rows in one statement. Lots of examples are around..
b) Your code is presumably processing a row with a null empid, which the trigger in turn tries to insert in to the department table. So either your code needs to get rid of the null empid, or the trigger needs top cater for it, or it should be nullable in the department table. Which is right depends on what you are doing...
Mike John
Thanks for your information Mike.
I just want to know whether the trigger is firing after the insertion or before the insertion?
Does INSERTED is a virtual table created automatically when the Insertion happens or Insertion about to happen?
I don't think the null value will come into scene.
Sorry, forgot to mention empID is autogenerated number, not inclued in Insert stmt on SP.
Thank You
March 18, 2009 at 8:53 am
The trigger as you have it (ie FOR INSERTED) is an after trigger and logically happens AFTER the update has been done, (but before the transaction has been committed).
The issue with multiple rows is caused by SELECT @tr_empID = (SELECT empID FROM Inserted) - which empid do you want?
Mike John
March 18, 2009 at 9:10 am
Mike John (3/18/2009)
The trigger as you have it (ie FOR INSERTED) is an after trigger and logically happens AFTER the update has been done, (but before the transaction has been committed).The issue with multiple rows is caused by SELECT @tr_empID = (SELECT empID FROM Inserted) - which empid do you want?
Mike John
Thanks Mike.
Yes, Of course, but the insertion is for only one record. The SP inserts only one record at a time. So there is no chance of multiple records.
Thank You
March 18, 2009 at 9:26 am
Looking at the insert statement the empId comes from t_EmpID on your holding table and not the identity column empID.
Can you confirm there are no NULLs in this column . Run the select statement on it's own to see what values are being inserted into the Employee table.
SELECT [t_EmpID],
[t_EmpName]
FROM [Emp].[dbo].[stg_tempEmp]
WHERE ISNULL(newRecFlag, '0') <> '1'
AND ISNULL(LTRIM(RTRIM(empID)),'') <> ''
AND recNum = @rcount
Then deal with the NULL value in whatever way is appropriate.
March 19, 2009 at 12:03 am
christine.dsouza (3/18/2009)
Looking at the insert statement the empId comes from t_EmpID on your holding table and not the identity column empID.Then deal with the NULL value in whatever way is appropriate.
Thanks a lot for your suggestions.
I am sure there is no NULL value comes into picture.
One more important thing is select statement have empID which is autogenerated. I am sorry for this. I unfortunately entered EmpID in select stmt.
I would like to know......what happens if the select stmt doesn't return no record.
INSERT INTO table1
(.....columnnames.....)
SELECT ......columnnames.........
FROM table2
WHERE condition1 AND condition2
In the above stmt, SELECT stmt didn't return any record so still the INSERT stmt executes?
Thank You
March 19, 2009 at 12:58 am
No the insert statement wont execute....
It seems there is a problem with the insert statement (column mismatch)
If possible provide us the department table's structure....
March 19, 2009 at 2:35 am
I think you may have spotted it. The trigger will indeed fire when zero rows are retrived in the example you post. Triggers always fire once per statement and the inserted (and/or deleted) tables will contain zero, 1 or many rows depending on how many rows were affected by the statement that caused the trigger to fire.
If the trigger is properly re-written to handle multiple rows then it will properly handle zero rows as well. Assigning a value from inserted in a trigger to a variable is never a good idea - you may think that only one row at a time is being inserted, but at some time something will happen that inserts multiple rows and the logic breaks. Also as the code below points out you reall y should put the column names in the insert statement.
I think all you need is something like..
CREATE TRIGGER tr_Insertempdept
ON [dbo].[Employee]
FOR INSERT
AS
INSERT into Dept.dbo.Department (you really should name the columns here!)
select empid, empname from inserted
Mike John
March 19, 2009 at 6:12 am
Mike John (3/19/2009)
I think you may have spotted it. The trigger will indeed fire when zero rows are retrived in the example you post. Triggers always fire once per statement and the inserted (and/or deleted) tables will contain zero, 1 or many rows depending on how many rows were affected by the statement that caused the trigger to fire.Mike John
Thanks to all for your valuable sugessions.
I am thinking in this way. I don't know whether it is a best practice or not.
In the above select statements, you have seen one flag newRecFlag. If that is not =1 then only that select stmt will get some records. So before Insert stmt, I am keeping a condition to verify the flag newRecFlag and it is not 1 then it will go to insert stmt else skip the insert stmt.
SELECT @newrecflag=newRecFlag FROM stg_tempEmp
WHERE EmpID=@t_empID AND recNum=@rcount
IF @newrecflag <> 1
BEGIN
INSERT INTO [Emp].[dbo].[Employee]
(
[EmpID],
[EmpName],
[EmpAddr],
[Empexp],
[EmpCtNo],
[EmpLoc]
)
SELECT [t_EmpID],
[t_EmpName],
[t_EmpAddr],
[t_Empexp],
[t_EmpCtNo],
[t_Emploc]
FROM [Emp].[dbo].[stg_tempEmp]
WHERE ISNULL(newRecFlag, '0') <> '1'
AND ISNULL(LTRIM(RTRIM(empID)), '') <> ''
AND recNum = @rcount
END
If the flag=1 then the select stmt gives no records this cuases trigger to check the Virtual table INSERTED and is taking NULL. I will skip the INSERT stmt itself then no problem right?
Thank You
March 19, 2009 at 9:47 am
While what you have may work at the momenet the statement:
SELECT @newrecflag=newRecFlag FROM stg_tempEmp
still worries me - what happens if there are many rows in stg_tempemp?
Mike John
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply