|
Home > Archive > SQL server exams > October 2002 > Coco's question of the Week #16
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Coco's question of the Week #16
|
|
| cocolocopolo 2002-10-24, 2:08 pm |
| Connnfusion between 'After' and 'Instead Of' trigger
Here is the scenario:
There are two tables: Categories table and Products table as shown below:
Create Table dbo.Categories (CategoryID int Identity (1, 1) not null, CategoryName nvarchar (15) not null, Description ntext null)
Create Table dbo.Products (ProductID int Identity (1, 1) not null, ProductName nvarchar (40) not null, Discontinued bit not null)
When a category is deleted, you want to mark the products associated with the category as discontinued rather than deleting them.
So, I am confused which one will be correct?
1. Create Trigger Discontinue_Product On Categories After Delete AS
Update Prod Set Discontinued = 1 From Products As Prod Inner Join Deleted As Del
On Prod.CategoryID = Del.CategoryID
or:
2. Create Trigger Discontinue_Product On Categories Instead Of Delete AS
Update Prod Set Discontinued = 1 From Products As Prod Inner Join Deleted As Del
On Prod.CategoryID = Del.CategoryID
And why? | |
|
| Column CategoryID doesn't exist on Table dbo.Products so both triggers will not work.
If you change the Products table definition to include CategoryID,
the first method with AFTER trigger will do the job.
"Instead of" trigger will update Products table, but Categories records will not be deleted | |
| 2lazybutsmart 2002-10-28, 2:08 am |
| short but helpfull MNF. nice job. | |
| cocolocopolo 2002-10-29, 12:27 am |
| Thanks dear MNF & 2lazybutsmart. |
|
|
|
|