[sqlite] INSTEAD OF Trigger Question

[Sylko Zschiedrich]

Hi all,

i have a question to "instead of triggers" on views.

Following schema:

-- View ----------------------------------------------
CREATE VIEW DepartmentHasApp as
 select
         UID_Application         as     UID_Application,
         UID_Org         as     UID_Department,
         XTouched        as     XTouched,
...
from BaseTreeHasApp where exists (select 1 from basetree y where
y.uid_org = BaseTreeHasApp.UID_Org and y.uid_orgroot = 'VDepartment');

-- Trigger ---------------------------------------------

CREATE TRIGGER DepartmentHasApp_Update Instead of Update on
DepartmentHasApp
begin
  update BaseTreeHasApp set
         UID_Application  = coalesce(new.UID_Application,
old.UID_Application),
         UID_Org          = coalesce(new.UID_Department,
old.UID_Department),
         XTouched         = coalesce(new.XTouched, old.XTouched),
...
  where UID_Application = coalesce(new.UID_Application,
old.UID_Application)
    and UID_Org = coalesce(new.UID_Department, old.UID_Department);
end;

----------------------------------------------------------

That's my current implementation. But with this I can't update the View
to 'null' values because the coalesce statement will return the
old.values.

How can I handle that? 
Can I "detect" the columns to update?




Thanks

Sylko 

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------