Creating a trigger to change the value to 'NULL'

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

SQL Server Development (2000)

Creating a trigger to change the value to 'NULL'

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-15 : 11:56:39

How do I create a trigger that will check columns 5, 7, 13 - 16 for the value '-1' and have it changed to a NULL (CTRL-0) value when an update happens?

I have an application that when a user submits a page of information and doesn't fill in certain fields (doesn't need to), it saves it as '-1'. Since I don't know to much about editing ASP pages, I just thought I might be able to do it through a trigger.

Thx, JLM

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 12:00:37

well u could also do it by putting a default value on your column in database. that way trigger isn't needed.

Go with the flow & have fun! Else fight the flow

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-15 : 12:21:12

I'm not sure if a default column would work. Are you thinking of a computed column??

The trigger would need to reference the INSERTED table to perform the operation.


UPDATE a
SET a.SomeColumn5 = CASE WHEN INSERTED.SomeColumn5 = -1 THEN NULL ELSE INSERTED.SomeColumn5 END,
SET a.SomeColumn7 = CASE WHEN INSERTED.SomeColumn7 = -1 THEN NULL ELSE INSERTED.SomeColumn7 END,
SET a.SomeColumn13 = CASE WHEN INSERTED.SomeColumn13 = -1 THEN NULL ELSE INSERTED.SomeColumn13 END,
...
FROM YourTable a INNER JOIN INSERTED ON (a.PrimaryKey = INSERTED.PrimaryKey)

Hope this helps

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-15 : 12:36:08

i meant default value. and why wouldn't that work? the only reason i can think of if he updates all columns
which is a possibility. but i don't see why would he do that...

Go with the flow & have fun! Else fight the flow

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-15 : 12:39:57

2 things...

1st it's be better if you called a stored procedure...

Ok, which is not the case,

2nd, the ddl would be a big help

Let's assume the column is defined as varchar(n)

For the trigger thing, you'll need an INSTEAD of TRIGGER for INSERTS and UPDATES....sounds messy....

Let's ask this first....how do they handle errors now...for example dup key?

What if you put contraints to prevent them from entering -1?

Or does this ALL fall on you?

What you're trying to do is....how did Dr. Frankenfurter put it....

Cure the sympton...not the cause....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-09-15 : 14:15:09

[code]
UPDATE U
SET MyCol5 = NullIf(MyCol5, -1),
MyCol7 = NullIf(MyCol7, -1),
...
FROM dbo.MyTable U
JOIN inserted I
ON I.MyPK = U.MyPK
[/code]
Kristen

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-15 : 14:17:03

Here's the table that I'm working with:

******************************************

CREATE TABLE [dbo].[iso_wi] (
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wi_title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rev_date] [smalldatetime] NULL ,
[author] [int] NULL ,
[author_dept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sup_approval] [int] NULL ,
[trform] [bit] NOT NULL ,
[sc] [bit] NOT NULL ,
[cam] [bit] NOT NULL ,
[lut] [bit] NOT NULL ,
[sp] [bit] NOT NULL ,
[sc_trainer] [int] NULL ,
[cam_trainer] [int] NULL ,
[lut_trainer] [int] NULL ,
[sp_trainer] [int] NULL
) ON [PRIMARY]
GO

******************************************

The columns that get a '-1' from that asp page are of type 'INT'.

I'm guessing that the asp page updates all the columns regardless if any of the
fields were left empty. I'm not sure why it doesn't use 'null' on the empty fields
instead of putting a '-1' back into the dB. These fields that appear to be storing
the '-1' value in the asp pages are only the 'drop down' menu types. When a user
goes to update some fields but leaves some at the default value 'Please Select -->' and doesn't choose anything, then a '-1' gets placed as the value.

JLM

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-15 : 14:38:57

WAIT..hold the phone....

You said the database "Appears" to be holding a -1.

Are you sure?

Did you do a select against the table to check?

Are you sure this is not a .net coding/display problem?

Brett

8-)

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-15 : 15:48:53

Sequence of events:

1: Start off by importing some data into the table. Some of those 'INT' fields that I mentioned doesn't get anything put into them so they become NULL after I do the import. I verified this by looking at the table afterwards.

2: A user goes to update some fields using the ASP application, but leaving some at default (not choosing from the dropdown menu). When he clicks on SUBMIT, it changes some of the data. The ones that were drop down menus that he didn't select, became '-1' in the database. So the NULL gets replaced by the asp application. I also verified this by looking at the table in afterwards.

Hope this helps.

JLM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-16 : 01:30:16

IMHO, the answer lies in how you process the data before storing.

Kristen
Test

22859 Posts

Posted - 2004-09-18 : 06:04:23

My trigger example code [above] will fix the "-1" in your columns, to NULL, if that's how you want to do it.

Kristen

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-20 : 12:22:16

Kristen - I found the asp page that is inserting that '-1' as the default (posted below). I belive this
would be the best way to fix these problem, but if you could still tell me how I would've
created this trigger, it would be nice to know.

--------------------------------------
CREATE TRIGGER iso_wi_null_tr
ON dbo.iso_wi
for UPDATE
AS
SET NOCOUNT ON
UPDATE U
SET author = NullIf(author, -1),
sup_approval = NullIf(sup_approval, -1),
...
FROM dbo.iso_wi U
JOIN inserted I
ON I.wi = U.wi
--------------------------------------

Also, if I use an 'instead of' type trigger, will it work since I already have a 'Update' trigger and a foriegn key on this table already? I read this in the help section:
"Note INSTEAD OF UPDATE triggers cannot be defined on a table that has a foreign key defined with an UPDATE action."

Here is a full generated script from SQL on the 'iso_wi' table:

-----------------------------------------------------
CREATE TABLE [dbo].[iso_wi] (
[wi] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[wi_title] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[rev] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[rev_date] [smalldatetime] NULL ,
[author] [int] NULL ,
[author_dept] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sup_approval] [int] NULL ,
[trform] [bit] NOT NULL ,
[sc] [bit] NOT NULL ,
[cam] [bit] NOT NULL ,
[lut] [bit] NOT NULL ,
[sp] [bit] NOT NULL ,
[sc_trainer] [int] NULL ,
[cam_trainer] [int] NULL ,
[lut_trainer] [int] NULL ,
[sp_trainer] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[iso_wi] WITH NOCHECK ADD
CONSTRAINT [PK_iso_wi] PRIMARY KEY CLUSTERED
(
[wi]
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER iso_wi_TR
ON dbo.iso_wi
for UPDATE
AS
SET NOCOUNT ON

UPDATE x
SET x.rev = i.rev
FROM iso_employwi x
inner join inserted i
ON x.wi = i.wi

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------

After looking around in the asp pages, I found the part that is inserting the '-1' as the defult if nothing else is choosen. If anyone knows asp, how do I rewrite this to tell it to insert a NULL (CTRL-0) into the data field?

response.write ">"
response.write "<option value=""-1"">Please Select --></option>"
if (bIsDatabase=1) then
if (sLookupValue<>"") then
set RecordSetOptions = Conn.execute(sSQL)
if (sChildDropdownLinkField<>"") and (sParentDropdownName<>"") and (sParentDropDownField<>"") then
if (iMode=1) then
iValue = RecordSet(sParentDropDownField)
elseif (iMode=0) then
iValue = sParentDefaultValue
if (iValue="") then
iValue=-1
end if
else
iValue = -1

Thanks alot for everyones help!

JLM

PS: How do I post to show the proper spacing/tabs? In wordpad, it looks fine, but when I post it here, it always makes it left justified.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-20 : 12:51:47

quote:

PS: How do I post to show the proper spacing/tabs? In wordpad, it looks fine, but when I post it here, it always makes it left justified.

Wrap your post in code tags. It is an option in the format toolbar.

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 13:33:37

Why do you want to use an INSTEAD OF trigger?

I haven't looked at the code carefully, but if you change the (two occurrences) of

iValue = -1

to

iValue = "NULL"

if might work!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 13:45:57

Anyone want to address the fact that we're not fixing the actual problem.

You've got to fix the asp code.

Don't burden SQL with someone elses problems.

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 14:11:04

I don't have too much of a problem with an SProc receiving a -1 to imply that a NULL should be stored.

In an application it can sometime be easier to say things like "If I give SQL a blank for a string I want it to be stored as a NULL, but if I give it NOTHING (i.e. parameter defaults to, say, NULL) then I want SQL to leave the current column value alone"

But maybe that's just me and you lot will tell me it stinks!

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-20 : 14:12:23

I make them pass me the word Null in the parameter string, unquoted

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-09-20 : 14:20:36

I agree that's ideal.

However, on the web applications we do the standard Web Forms return an empty string if the user leaves a form field empty.

So we allow those all the way through to the SProcs, where they get converted to NULLs.

Quite handy really, we have an SProc called "MyTable_SAVE". That requires the PK to be speciifed and then any, or all, columns in that table. Those that are NOT provided default to NULL (in the Sproc parameters). Those that ARE provided either have a value, or are a blank string.

If a value is provided, or a blank string, the column is set to that value [blank becomes NULL]; if a value is not provided for a column the parameter will be NULL, and the value for that column will be unchanged.

If you get my meaning ...

Kristen

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-20 : 15:37:45

If I change it to "0", it works and saves the value of 0. I tried it with "Null", and it gave me this error:

Error Type:
Provider (0x80020005)
Type mismatch.
/isomatrix/includes/datafields.asp, line 439

I am also now searching 'google' for help.

JLM

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2004-09-20 : 19:24:05

I couldn't find what I was looking for by searching on the web on how to correct/fix an asp page on how to enter a 'null'. I did find some examples, but just didn't work for me. Maybe this asp page is connected to another one and thats why some things just won't work. Anways, thanks for all your help. Kristen, I created an 'insert/update' trigger and it seems to do what I want. So I guess this is like a "band-aid" for the time being.

JLM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-20 : 23:33:31

this is quite confusing... allow me to clear the field,

you have a control that returns an integer value if user choose something and you save that value? and i guess this value has a corresponding description or name on another table but not on the one it was saved.

how about if you don't pass anything, like if it's -1, then don't pass the -1, if the data will be inserted into the table, without the value, the value for that column will be null?

just an idea that you may want to try...

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources