Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Null exception thrown on insert trigger?

Author  Topic 

dpnadam
Starting Member

29 Posts
Posted - 2011-02-09 : 04:32:02
Hi

I have the following insert trigger. Basically when a customer has been inserted to Database1 - Customer table, I need to insert the new customer into another database(Database2) - Customer table. However, when I insert the new customer through the software an error is raised: "Cannot insert the value NULL into column 'Surname', table 'Database2.dbo.Customer': column does not allow nulls. INSERT FAILS." Any ideas why this is happening?


CREATE TRIGGER [dbo].[tr_INSERT_CustomerInDB2] 
   ON  [dbo].[Customer]
   AFTER INSERT
AS 
BEGIN

   SET NOCOUNT ON;
   
   INSERT INTO [Database2].[dbo].[Customer]([ID], [Surname], [Firstname])
   SELECT [ID], [Surname], [Firstname] FROM [inserted]

END
GO


Thanks

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-02-09 : 04:35:07
The software is passing NULL for the surname. Use Profiler to see exactly what queries are getting executed.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts
Posted - 2011-02-09 : 04:37:49
the message is clear enough and telling you that you/your application is trying to pass NULL values into the Customer's SurName column which is not allowed. seems that the SurName is declared with NOT NULL constraint. Therefore make sure NULL values are not passed into this table any way ...

Cheers
MIK
Go to Top of Page

dpnadam
Starting Member

29 Posts
Posted - 2011-02-09 : 04:40:46
quote:
Originally posted by GilaMonster

The software is passing NULL for the surname. Use Profiler to see exactly what queries are getting executed.

--
Gail Shaw
SQL Server MVP


Thanks for replying Gail. Sorry, I realise it is passing NULL for the surname, I just didn't know why as I was using AFTER INSERT so I thought there would be a value there to be passed. I'll have a look at Profiler, the one by AnjLab?

Thanks.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-02-09 : 05:01:31
quote:
Originally posted by dpnadam

Thanks for replying Gail. Sorry, I realise it is passing NULL for the surname, I just didn't know why as I was using AFTER INSERT so I thought there would be a value there to be passed.


???

The application is passing NULL as the insert to the first table. The trigger just writes the exact same values over to the second table. So if the app passes NULL for surname it will be null into both tables.

I don't understand why you think there would be a value.


quote:
I'll have a look at Profiler, the one by AnjLab?


Err, no. The one that's installed with SQL Server.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

dpnadam
Starting Member

29 Posts
Posted - 2011-02-09 : 05:19:48
quote:
Originally posted by GilaMonster
The application is passing NULL as the insert to the first table. The trigger just writes the exact same values over to the second table. So if the app passes NULL for surname it will be null into both tables.


Apologies for my confusion. I wasn't thinking of NULL being inserted into the first table as before creating the trigger I could insert a customer through the software just fine. i.e. Before the trigger, I could click the new customer button, box opened, enter details, confirm fine. After creating the trigger, as soon as I click the button the error came up. But I suppose may be the software is inserting some default values for a new customer to begin with. The first table allows nulls for the field whereas the destination table doesn't.

quote:
Err, no. The one that's installed with SQL Server.


Sorry, I'm using MSSM Studio Express so I couldn't see the profiler.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-02-09 : 09:29:34
Are you entering a surname?

The reason it worked before you added the trigger is what you mentioned at the end
quote:
The first table allows nulls for the field whereas the destination table doesn't.


--
Gail Shaw
SQL Server MVP
Go to Top of Page

dpnadam
Starting Member

29 Posts
Posted - 2011-02-09 : 10:23:37
quote:
Originally posted by GilaMonster

Are you entering a surname?


I wasn't getting a chance to enter a surname. As soon as I clicked the new customer button, before the details box opened, it threw the null exception error.

I found that as soon as you click the button a record is being inserted into the customer table with some default values, CustomerNumber etc, which was obviously then firing my trigger. I've changed it to an update trigger now and altered the code accordingly so it is all working fine now.

Thanks.
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts
Posted - 2011-02-09 : 13:26:36
quote:
Originally posted by dpnadam

I found that as soon as you click the button a record is being inserted into the customer table with some default values, CustomerNumber etc, which was obviously then firing my trigger.

Terrible coding practice


quote:
I've changed it to an update trigger now and altered the code accordingly so it is all working fine now.


Cool. Just note that every time something changes you'll get a new row in the second table. Is that what you want?


--
Gail Shaw
SQL Server MVP
Go to Top of Page

dpnadam
Starting Member

29 Posts
Posted - 2011-02-10 : 05:41:02
quote:
Originally posted by GilaMonster

quote:
Originally posted by dpnadam

I found that as soon as you click the button a record is being inserted into the customer table with some default values, CustomerNumber etc, which was obviously then firing my trigger.

Terrible coding practice


I agree, unfortunately the software isn't something I have developed and can control.

quote:

quote:
I've changed it to an update trigger now and altered the code accordingly so it is all working fine now.


Cool. Just note that every time something changes you'll get a new row in the second table. Is that what you want?



No, but I have altered the code so that it will update existing rows or insert new ones if it doesn't exist.

Thanks.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site