• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Trigger - needs to deal with nulls

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
PtboGiser
Asked:
PtboGiser
  • 7
  • 6
  • 2
  • +1
1 Solution
 
nost2Commented:
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
 
Jared_SCommented:
You should seriously consider giving these columns a default value of 0 in your table.
0
 
ralmadaCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ralmadaCommented:
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
 
PtboGiserAuthor Commented:
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
 
ralmadaCommented:
>>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
 
PtboGiserAuthor Commented:
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
 
ralmadaCommented:
Oh I see. Ok, let us know if you have more questions.
0
 
PtboGiserAuthor Commented:
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
 
ralmadaCommented:
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
 
PtboGiserAuthor Commented:
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
 
PtboGiserAuthor Commented:
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
 
ralmadaCommented:
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
 
PtboGiserAuthor Commented:
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
 
ralmadaCommented:
>>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
 
Jared_SCommented:
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now