February 23, 2005 at 8:52 am
Title pretty much explains it, but here is more detail. I have an Insert trigger that is supposed to check to see if X, Y, and Z fields had values when the Insert occurred. If not, look up the last related record and copy that value into the new record. This works fine for most fields. The one field is does not work on happens to be the one that does not allow Null, and defaults to "(None)".
I am trying to figure out where the default comes into play, and how I can have the trigger do what it is supposed to do, carry forward the value of the previous record, without the default value putting itself into the mix.
This table has lots of other defaults, but none of them are involved with this Insert trigger.
Relevant table info:
[EeFoundSource] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
ALTER TABLE [dbo].[EEmploy] ADD
CONSTRAINT [DF_EEmploy_EeFoundSource] DEFAULT ('(None)') FOR [EeFoundSource],
Relevant info from trigger:
SELECT @FLXID = inserted.eeflxid,
@FLXIDEB = inserted.eeflxideb,
@EeDateBeg = inserted.eedatebeg,
@FoundSource = inserted.eefoundsource
FROM Inserted
IF DATALENGTH(RTRIM(@FoundSource)) IS NULL OR
DATALENGTH(RTRIM(@FoundSource)) = 0
UPDATE EEmploy
SET EeFoundSource =
(SELECT EeFoundSource FROM EEmploy
WHERE EeFlxIDEb = @FLXIDEB
AND EeDateEnd = DateAdd(d,-1,@EeDateBeg))
WHERE EeFlxID = @FLXID
Thanks,
Chris
February 23, 2005 at 8:59 am
So you don't have control over the table design, and can't make the column nullable with no default ?
Your trigger code looks like it only works for singleton insertions of 1 row each. What happens if several records are inserted to this table at once ?
February 23, 2005 at 9:43 am
OK, nevermind, I have worked this out.
I do have control over the table design, and the user has now said that they would rather have a Null instead of the default value of "(None)". This fixes the immediate problem.
I have also realized that in a case where I need to have no Null, and a default value, I just need to account for it within the trigger, and let the trigger set the default, if needed, rather than putting the default in the Table properties.
In the case of this trigger, yes, it is supposed to only work for single inserts. The program that does the inserts only does them one at a time.
Thanks,
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply