holemania
asked on
SQL - Insert and Update Trigger
I need to write a trigger that will copy one field to another when there's an insert or update. Since this insert/update is being done by the application, which I have no control over. Is it possible to do an after trigger for just an update?
Example, there's 2 date field (Delivery_Date and Due_Date). If Delivery_date is populated, then the trigger will also populate Due_Date. However, if it's an update and Due_date is already populated, then do not update
Here's what I'm thinking of, but does not work.
Create Trigger dbo.Update_date
On dbo.Purchase_line
After Update
Update Purchase_line
Set Delivery_date = Due_Date
From Purchase_line PL
Where PL.Order_ID in (Select I.Order_Id from Inserted I Where I.Order_ID = PL.Order_ID
I.Line_no = PL.Line_no)
and PL.Due_Date Is Null
The above, I want it to execute after the Insert/Update is done then fire the above to do an update if "Due_Date" is Null only.
Example, there's 2 date field (Delivery_Date and Due_Date). If Delivery_date is populated, then the trigger will also populate Due_Date. However, if it's an update and Due_date is already populated, then do not update
Here's what I'm thinking of, but does not work.
Create Trigger dbo.Update_date
On dbo.Purchase_line
After Update
Update Purchase_line
Set Delivery_date = Due_Date
From Purchase_line PL
Where PL.Order_ID in (Select I.Order_Id from Inserted I Where I.Order_ID = PL.Order_ID
I.Line_no = PL.Line_no)
and PL.Due_Date Is Null
The above, I want it to execute after the Insert/Update is done then fire the above to do an update if "Due_Date" is Null only.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER