One source table column value to populate two destination fields 2

[bustercoder]

Hello,
I have this trigger which inserts a line for a freight amount into another table, but I need to update two fields with the same value. This value for the freight amount only comes in as one column from the source table. Is there a way to populate the second field with that value? The first value (SOP10102.CRDAMT) populates fine with the FREIGHT_AMOUNT, but the next column (SOP10102.ORCRDAMT) returns a "does not allow nulls" error, and making that column to allow nulls is not an option. You can see from my trigger below how I have the FRIEGHT_AMOUNT in twice, because I thought I could map it however many times I needed to whatever fields I needed.

(CREATE TRIGGER [dbo].[trig_insertFreightLine] ON dbo.Test_Shipments_Import
FOR INSERT, UPDATE, DELETE
AS
begin
SET NOCOUNT ON

if exists (select * from deleted)
begin
delete SOP10102
from SOP10102 ifl inner join
deleted on (ifl.SOPNUMBE = deleted.ORDER_ID)
end

if exists (select * from inserted)
begin
insert SOP10102
select distinct 3, ORDER_ID, 100, 7, '', 83, 0, 0, FREIGHT_AMOUNT, FREIGHT_AMOUNT, 2999, '', 0
from inserted
end
end

Thanks,
Buster

 

[ESquared]

because I thought I could map it however many times I needed to whatever fields I needed.

You can do this. If you're getting a NULL violation, it means that FREIGHT_AMOUNT is null. What behavior do you want to have if it is null? Obviously you can't insert null to that table. Insert a zero instead?

Second, why are you using a trigger to enforce deletes? This appears something you can do with database constraints as long as SOP10101.SOPNUMBE is an FK to order ID.

ALTER TABLE SOP10101 ADD CONSTRAINT FK_SOP10101_SOPNUMBE_ORDERID FOREIGN KEY (SOPNUMBE) REFERENCES Test_Shipments_Import(ORDER_ID) ON DELETE CASCADE





[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[bborissov]

Then you have NULL in FREIGHT_AMOUNT or your fields in SOP10102 is not in the same order as in Test_Shipments_Import.
What happens if you explicitly set the field list:

Insert INTO SOP10102 (3_Field, ..., CRDAMT, ORCRDAMT, ...)
select distinct 3, ..., FREIGHT_AMOUNT, FREIGHT_AMOUNT
       from inserted

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bustercoder]

ESquared (nice name), you are absolutely correct about using a constraint for the delete, but these are financial tables which we're hesitant to mess with too much because of all the dependencies. However, perhaps I should look into it because it is definitely a better solution than handling it in the trigger. I don't have a null value in the FREIGHT_AMOUNT, so perhaps I need to explicitly map the fields and make sure they're aligned as Borislav suggests. In any case, I appreciate your (quick) feedback very much.