SQL

Avdhesh Gupta

12y1.1kSQL

create an INSTEAD OF trigger, Sales.tr_ArchiveOrders, that watches the Sales.Orders table for a DELETE and instead does the following:

1. Copies all relevant rows from Sales.Orders to Sales.OrdersArchive

2. Copies all relevant rows from Sales.OrderDetails to Sales.OrderDetailsArchive

3. Deletes those rows from Sales.OrderDetails

4. Finally, deletes the relevant rows from Sales.Orders

orderid

custid

empid

orderdate

requireddate

shippeddate

shipperid

freight

shipname

shipaddress

shipcity

shipregion

shippostalcode

shipcountry

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Avdhesh Gupta12y ago
Because of the foreign key constraint in the Sales.OrderDetails table on the orderid column, you know you cannot delete a row from Orders without first deleting all rows with the same orderid value from OrderDetails. You create a table Sales.OrderDetailsArchive that has the same columns / data types as Sales.OrderDetails, plus the Archived column of type datetime with default value CURRENT_TIMESTAMP. To solve the deletion problem, create an INSTEAD OF trigger, Sales.tr_ArchiveOrders, that watches the Sales.Orders table for a DELETE and instead does the following: 1. Copies all relevant rows from Sales.Orders to Sales.OrdersArchive 2. Copies all relevant rows from Sales.OrderDetails to Sales.OrderDetailsArchive 3. Deletes those rows from Sales.OrderDetails 4. Finally, deletes the relevant rows from Sales.Orders
Guest User12y ago
Do you want me to share code of trigger, I can but please clarify your statement, this made me confuse. What is this INSTEAD OF, what's the criteria to move relevant data? Share class diagram of tables , which shows joins etc

create an INSTEAD OF trigger, Sales.tr_ArchiveOrders, that watches the Sales.Orders table for a DELETE and instead does the following:
Avdhesh Gupta12y ago
I need trigger only
if you can
Guest User12y ago
you can use cursors for this operation in combination of select, e.g.,

if you want to select data from one table and insert into other, so look for cursors implementation

if you need more details i can provide.