After insert trigger on a table that has a Primary Key

[Ahmad Saleh]

Hi All

I have the following 2 tables:

1. Order with the following columns: OrderID as PK, Order Name, etc.

2. Delivery with the following columns: DeliveryID as PK, OrderID, date, etc.

I wrote after update trigger on the Order table that inserts a new row to the Delivery table and fills it with the OrderID:

create Trigger CopyOrderID_To_DeliveryTableon Order
for insert 
as 
begin
    declare @ID int

    select @ID = REQUESTID from inserted  

    insert into Delivery (OrderID) 
    values (@ID)
end

This should work fine but the issue here is that the DeliveryID column in the Delivery table is a non-nullable PK. So whenever I try to update the Order table, I get an error message that DeliveryID in the Delivery table doesn’t allow nulls!

How to get rid of this message without altering the DeliveryID column?

A screenshot shows the DeliveryID column properties :

Thank you,

Ahmad

[#2 · Accepted solution]

Hi Ahmad, 

I'm not sure to fully understand your need, but please consider the following bullet :

- From performance perspective, triggers are not the best way to go.

- Why don't you allow your Order record to be childless, and then add a record into the Delivery table after, via user interface typically ? 

Finally, to keep your solution in place : 

- Define your DeliveryID column as a BigInt, and enable the Identity property to True (ref your screenshot). Then the value of your DeliveryID column will be automatically incremented for each new record.

- Define your DeliveryID column as a UniqueIdentifier, and define a default value as new_sequentialid().

Hope this helps.

Fred.M.

[#3]

Thank you, for the suggestions