Most of the time triggers are used to log the rows which are deleted by users. This can be very expensive if your table is large and also have many columns. However, this can be easily handled with the help of the output clause if we are deleting the complete row.
There are indeed certain advantages to the triggers like we can monitor and manage the change at the column level. However, in the case of the DELETE, most of the time we are deleting the entire row, and in most cases where I have seen the OUTPUT clause with the deleted keyword is a good enough solution.
Here in the video, I explain how the OUTPUT works with the DELETE statement.
You can download the script used in the demonstration from How to Capture Deleted Rows Without Trigger? – Interview Question of the Week #297.
Here is another blog post where I explain how you can Insert Multiple Values into Multiple Tables in a Single Statement – SQL in Sixty Seconds #132.
If you like my videos, I request you to like, share, comment, and subscribe to these videos. Here is the link to my YouTube Videos.
Reference: Pinal Dave (https://blog.sqlauthority.com)