Not null and triggers - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Not null and triggers

Author

Topic

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-20 : 10:15:14

Hi, If there's a column with a not null setting, then is there a method by trigger to avoid the "CANNOT INSERT NULL VALUE" error message that you'd get from, eg.

INSERT into yourtable (notnullcolumn) VALUES (NULL)

-------
Moo. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-20 : 10:32:48

You mean just not perform the insert and not report any error to the user? Or fill in the null value(s) with default values and then perform the insert? To answer your question, one way is to could use an INSTEAD OF trigger.
You aren't trying to cover for impropper error handling by an application, are you? what are you trying to accomplish?

Be One with the Optimizer
TG

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-20 : 10:41:45

Yes, essentially to cover up for application failings the idea is to replace the null values with A.N.Other value and not report any message to the user.

My quick test with the INSTEAD OF trigger still reported an error, it looked like it was still trying to insert the row that should have been not inserted, if you get what I mean.

-------
Moo. :)

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-20 : 10:44:06


CREATE TABLE [dbo].[Table1] (
	[test] [int] NOT NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER [TRIG2] ON dbo.Table1 
INSTEAD OF  INSERT, UPDATE, DELETE 
AS
INSERT INTO table1 values (4)


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

insert into table1 values (null)

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'test', table 'dbo.Table1'; column does not allow nulls. INSERT fails.
The statement has been terminated.

-------
Moo. :)

Kristen
Test

22859 Posts

Posted - 2005-04-20 : 11:25:25

Sounds pants as an idea, but INSERT into a VIEW that has an INSTEAD OF trigger?

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-20 : 13:59:01

Yeah..it sees the constraint before it evcen tries..I thought


USE Northwind
GO
SET NOCOUNT ON
CREATE TABLE [dbo].[Table1] (
	[test] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TRIGGER [TRIG2] ON dbo.Table1 
INSTEAD OF  INSERT, UPDATE
AS
INSERT INTO table1(test) SELECT COALESCE(test,4) FROM inserted
GO

insert into table1 values (null)
GO

SET NOCOUNT OFF
DROP TABLE Table1
GO

But no dice

Can you change the Table DDL to use DEFAULT?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2005-04-20 : 13:59:51

quote:
Originally posted by Kristen

Sounds pants as an idea..

The Queens English?

What the he|| does that mean?

Brett

8-)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-20 : 14:13:36

I hadn't actually messed with instead of triggers before but the only way I could do this was to create view with a computed column. One thing that was pretty weird was that after eliminating Nulls from the insert the @@Rowcount value didn't match the actual rows inserted:


set nocount on
create table junk (col1 int not null)
go

create view junk_v as 
select col1=col1+0 --computed column
from junk
go

create trigger trInsJunk ON junk_v instead of insert
as
insert	junk (col1) 
select	col1 
from	inserted 
where	col1 is NOT NULL
GO

insert junk_v (col1) 
select 1 union 
select 2 union 
select null union all
select null


select actualRows = (select count(*) from junk), @@Rowcount [@@Rowcount]

select * from junk_v
select * from junk
go

drop view junk_v
drop table junk

Be One with the Optimizer
TG

Kristen
Test

22859 Posts

Posted - 2005-04-21 : 02:10:53

Pants = Rubbish.

I have to admin I haven't actually heard the queen use that expression!

Kristen

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2005-04-21 : 02:51:11

She may well have used the word pants though - just perhaps not in public

A sarcasm detector, what a great idea.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-21 : 08:17:43

"Yes, essentially to cover up for application failings the idea is to replace the null values with A.N.Other value and not report any message to the user"

Bad idea. Better to catch the error and throw it back at user. Make those lazy developers earn their cash! :)

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-22 : 06:50:46

Thanks chaps. Mainly what I needed was to be able to say that it wasn't possible directly using a trigger. Proper default constraints have been applied to the table now.

-------
Moo. :)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-22 : 07:47:55

A Default was my first thought but in your original post, you said you wanted to avoid the error when this command was given:
INSERT into yourtable (notnullcolumn) VALUES (NULL)

Just to be clear to anyone reading this, a Default will not prevent the error if NULL is explicitly inserted into a not null column. Of course, the onus should fall on the application to catch and handle the error appropriately. Or better yet not even allow the attempt. Anyway, sounds like you're all squared away :)

Be One with the Optimizer
TG

ctadlock
Starting Member

2 Posts

Posted - 2005-05-02 : 22:18:37

I have a view that has an INSTEAD OF inserted trigger on it to handle inserting the data into the base table. The base table and the view include a column that can be null (like an identity column). When I insert into the view without passing in a value for the nullable column, I get an error back stating that I must supply a value (even though the column is an identity column). I've tried setting a default value on the column (if it isnt an identity) column, but that doesn't seem to work either. Is there any way around this?

Craig Tadlock
Ramp Technology Group
[url]http://blog.rampgroup.com[/url]

ctadlock
Starting Member

2 Posts

Posted - 2005-05-11 : 21:56:05

I figured this out, check out the solution at http://blog.rampgroup.com

Thanks
CT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources