Shortly: Very.
I hate the idea of instead of triggers and I made a couple of blog posts several times. And even worse is the implementation behavior on MS SQL. And the cascade constraints are bad too.
Let’s suppose this simple definition.
create table master(id int primary key, foo nvarchar(20)); create table detail(id int primary key, id_master int not null, bar nvarchar(20)); alter table detail add foreign key (id_master) references master(id) on delete cascade;
Nothing special. Works fine, no problems expected. Until you try to define instead of delete trigger on detail table. I.e.
create trigger tr_test on detail instead of delete as begin select 1; -- now the fun begins end
You get an nice error: Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'tr_test' on table. What the hell???
'detail'. This is because the table has a FOREIGN KEY with cascading DELETE or
UPDATE.
Why?
Again I got a direct proof, that in this area the MS SQL is wrong, very wrong. I would be willing to accept the limitation when I’ll be doing there some master table manipulation (yes, as I said, cascade constraints are bad too). But this? I can hardly believe my eyes. The trigger is almost empty. Or am I missing someting on backround that limits this to work?
Another “feature” that makes me love Firebird more.
OK, so let’s assume that this will be possible. What behaviour will you expect? You are saying that “would be willing to accept the limitation when I’ll be doing there some master table manipulation”. Actually you do. Your instead of trigger above is effectivelly swallowing delete in detail table meaning that if delete in master will be allowed in this case you will get orphan records in detail table on you will break data consistency.
From my perspective this behaviour is quite logical as I see these two constrains contradicting each other. You either want to redirect detele operation in INSTEAD OF trigger to some other logic (usually to replace original DELETE operation with UPDATE) or you want CASCADE DELETE. If you do not want to redirect DELETE operation to some other logic (and cancelling original DELETE) you would use normal TRIGGER ON DELETE.
I think, that cascade delete in FK constraint and instead of trigger on table are two not related pieces.
Let’s assume that my FK column allows null values. Then I can simply in trigger set it to null and the master delete operation succeeds (yes, I know I can do it directly with constraint). This scenario is perfectly valid and I’m still unable to create it.
I still think it’s wrong, but your comment made me realize something. I’m probably looking at the problem from more higher/design level. I see trigger as action started by database *instead of* the real operation. So the database at some level still thinks it’s performing delete, but the lower level is simply throwing it away and running the trigger. Nothing more, nothing less. This architecture in my mind probably comes from the before/after trigger designs in other databases, just converted to instead of thinking.
It’s same with “potential” circular cascade updates/deletes. Yes I know the total shit can happen, but I’m developer, I know what I want (and if not, it’s my fault, I should read something about it).
Maybe the MSSQL is trying to keep me in safe boundaries too much.
the reason is instead of trigger works just with views that’s all.