Solved

Trigger - needs to deal with nulls

Posted on 2013-01-09
16
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

 
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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

717 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