SQL

Question

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

Avdhesh Gupta · Answer

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

Sumit Jolly · Answer

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 Gupta · Answer

I need trigger only if you can

Sumit Jolly · Answer

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.