Solved

Trigger - needs to deal with nulls

Posted on 2013-01-09
16
212 Views
Last Modified: 2013-01-24
I have the following Trigger. I have not figured out how to write it properly including null and "0" values.
The Triggers need is to populate the LEFT_ODD Column with a 1(True) if the L_F_ADD is an ODD Number.
My problem is when i insert a new record into the table i do not populate the L_F_ADD, L_T_ADD, R_F_ADD, and R_T_ADD columns right away it may sit as a "null" or "0" value for a couple days. If its a null value i would like it to be changed to "0" as you fire dispatch system does not like "nulls".
I would like this trigger to do both functions for me. First Change the null value to a "0" then popluate the LEFT_ODD column if the L_F_ADD is a odd number.
Thanks

USE [CNTY_GIS]
GO
/****** Object:  Trigger [dbo].[LeftODD_Update]    Script Date: 01/09/2013 09:25:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[LeftODD_Update] 
ON [dbo].[Segment]
AFTER INSERT, UPDATE 
AS

UPDATE SEGMENT 
SET SEGMENT.Left_ODD = 1
WHERE (([L_F_add] % 2) = 1) OR
([L_F_add] = 0 AND [R_F_add] > 0 AND ([R_F_add]%2) = 0)); 

---OR ELSE IF [L_F_add] = null make it '0'
--OR  IF [L_T_add] = null make it '0'
 --IF [R_F_add] = null make it '0'
 --IF [R_T_add] = null make it '0'

Open in new window

0
Comment
Question by:PtboGiser
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 4

Expert Comment

by:nost2
ID: 38759056
You can update columns with null values like this:

UPDATE SEGMENT
SET Left_ODD = 0
WHERE Left_ODD is null

Also, you might want to use default values on the table:

create table SEGMENT (Left_ODD bit null default 0, L_F_add int null default 0)
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38759193
You should seriously consider giving these columns a default value of 0 in your table.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38759230
I wouldn't use a trigger for that, you can just use computed columns

create table segment (
col1,
col2,
left_odd bit as case when [L_F_add] % 2 = 1 OR
                                        ([L_F_add] = 0 AND [R_F_add] > 0 AND ([R_F_add]%2) = 0))
                                then 1 else 0 end persisted
)

Now if you still want to use a trigger then use the default value as explained by other experts, but don' update the whole table in your trigger as you're currently doing, just the record being updated/inserted , so change your update to

UPDATE a
SET a.Left_ODD = case when ((b.[L_F_add] % 2) = 1) OR
(b.[L_F_add] = 0 AND b.[R_F_add] > 0 AND (b.[R_F_add]%2) = 0)) then 1 else 0 end
from Segment a
inner join inserted b on a.id = b.id -- adjust to your identifier column name
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38759389
or without changing your table just re-create the left_odd column

alter table segment drop column left_odd;

alter table segment add left_odd bit as case when [L_F_add] % 2 = 1 OR
                                        ([L_F_add] = 0 AND [R_F_add] > 0 AND ([R_F_add]%2) = 0))
                                then 1 else 0 end persisted
0
 

Author Comment

by:PtboGiser
ID: 38760379
Using a default value would seem to make the most sense.
Taht being said i would only want to default the new inserted records not the exsiting.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38760480
>>Using a default value would seem to make the most sense.<<

Avoiding a trigger is what would make the most sense, when you can go with a computed column as I've mentioned http:#a38759389
0
 

Author Comment

by:PtboGiser
ID: 38760629
I am working towards the computed column i first need to make the default for each of these columns 0 upon insert.
Second step is the computed LEFT_ODD column
Thanks Ral
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38760657
Oh I see. Ok, let us know if you have more questions.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:PtboGiser
ID: 38760670
If i have set the Defaults for the L_F_ADD column to = 0. When i add a new record why would it not be populating with a 0? I'm confused!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38760721
how did you set that column?

it should have been something like

alter table segment
alter column L_F_Add int not null default(0);
0
 

Author Comment

by:PtboGiser
ID: 38762851
Hey Ral
I used the following
ALTER TABLE [dbo].[Segment_NoLock] ADD DEFAULT 0 FOR [L_F_ADD]

Open in new window


I get the following Error
Msg 1781, Level 16, State 1, Line 1
Column already has a DEFAULT bound to it.

Which makes sense as i built default vaules yesterday. So the Default is Built, Why would they not work?

There is also is design view option to set a default i used both as a test niether work.
0
 

Author Comment

by:PtboGiser
ID: 38762874
Ral
when running yours code i receive the following error message
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 38762988
ok, since you've already defined the default constraint, then I would suggest the following:

first get rid of the existing nulls

update [dbo].[Segment_NoLock] set [L_F_ADD] = 0 where [L_F_ADD] is null

then change alter the column

alter table [dbo].[Segment_NoLock] alter column [L_F_ADD] int not null
0
 

Author Comment

by:PtboGiser
ID: 38763650
I currently do no have any nulls in the Table so its clean that way.
I have about 7 column default values set up in this table and non are working after an insert using GIS front end Software. I must be missing something.

I simply am drawing a line using the GIS tool which of course inserts a new records in to the SQL table which is all working fine. But no defaults.
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 38763677
>>I have about 7 column default values set up in this table and non are working after an insert using GIS front end Software. I must be missing something.<<

first, please explain what you mean by "not working"

Second have you set the column to accept only not null values as I've mentioned above?

alter table [dbo].[Segment_NoLock] alter column [L_F_ADD] int not null
0
 
LVL 12

Expert Comment

by:Jared_S
ID: 38763878
Sorry to butt-in guys, but just check one of the column's properties in SSMS to see if the default value is set up as suggested.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now