So sehen die Trigger in der SQLite Version aus:
CREATE TRIGGER t_bd_idUser
before delete on idUser
begin
delete from idCatalogItem where
UserGUID = old.GUID
;
delete from idProp where
UserGUID = old.GUID
;
delete from idPropGroup where
UserGUID = old.GUID
;
delete from idUserProp where
UserGUID = old.GUID
;
delete from idImageCard where
UserGUID = old.GUID
;
delete from idImageSubscription where
UserGUID = old.GUID
;
delete from idUserFavorite where
UserGUID = old.GUID
;
delete from idUserUsage where
UserGUID = old.GUID
;
delete from idUserUser where
UserGUID = old.GUID
;
delete from idUserUser where
UserGUID2 = old.GUID
;
delete from idImageBoxUsage where
UserGUID = old.GUID
;
delete from idImageModel where
UserGUID = old.GUID
;
delete from idImageScore where
UserGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idPropGroup
before insert on idPropGroup
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idPropGroup
before update of GUID,UserGUID on idPropGroup
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bd_idPropGroup
before delete on idPropGroup
begin
delete from idPropGroupPathContent where
GUID = old.GUID
;
delete from idPropGroupPeriodContent where
GUID = old.GUID
;
delete from idPropGroupContent where
GUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idPropGroupPathContent
before insert on idPropGroupPathContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
end;
CREATE TRIGGER t_bu_idPropGroupPathContent
before update of FilePath,GUID,PropType on idPropGroupPathContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
end;
CREATE TRIGGER t_bi_idPropGroupPeriodContent
before insert on idPropGroupPeriodContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
end;
CREATE TRIGGER t_bu_idPropGroupPeriodContent
before update of GUID,Period,PropType on idPropGroupPeriodContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
end;
CREATE TRIGGER t_bi_idPropGroupContent
before insert on idPropGroupContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
select case when
((select 1 from idProp where
GUID = new.PropGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER t_bu_idPropGroupContent
before update of GUID,PropGUID,PropType on idPropGroupContent
begin
select case when
((select 1 from idPropGroup where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPropGroup'
)
end;
select case when
((select 1 from idProp where
GUID = new.PropGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER t_bi_idCatalogItemDefinition
before insert on idCatalogItemDefinition
begin
select case when
((select 1 from idProp where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.CatalogItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idCatalogItemDefinition
before update of CatalogItemGUID,GUID on idCatalogItemDefinition
begin
select case when
((select 1 from idProp where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.CatalogItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bi_idUserProp
before insert on idUserProp
begin
select case when
((select 1 from idProp where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idUserProp
before update of GUID,UserGUID on idUserProp
begin
select case when
((select 1 from idProp where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idImageCard
before insert on idImageCard
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idImageCard
before update of GUID,ImageGUID,UserGUID on idImageCard
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bi_idImageSubscription
before insert on idImageSubscription
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idImageSubscription
before update of GUID,ImageGUID,UserGUID on idImageSubscription
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bi_idImageIPTC
before insert on idImageIPTC
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idImageIPTC
before update of GUID,ImageGUID on idImageIPTC
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bd_idPlaceHolder
before delete on idPlaceHolder
begin
delete from idVersionPlaceHolder where
PlaceHolderGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idVersionPlaceHolder
before insert on idVersionPlaceHolder
begin
select case when
((select 1 from idPlaceHolder where
GUID = new.PlaceHolderGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPlaceHolder'
)
end;
select case when
((select 1 from idImageVersion where
GUID = new.ItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageVersion'
)
end;
end;
CREATE TRIGGER t_bu_idVersionPlaceHolder
before update of ItemGUID,PlaceHolderGUID on idVersionPlaceHolder
begin
select case when
((select 1 from idPlaceHolder where
GUID = new.PlaceHolderGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idPlaceHolder'
)
end;
select case when
((select 1 from idImageVersion where
GUID = new.ItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageVersion'
)
end;
end;
CREATE TRIGGER t_bi_idImageComment
before insert on idImageComment
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idImageComment
before update of GUID,ImageGUID on idImageComment
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bi_idUserFavorite
before insert on idUserFavorite
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idUserFavorite
before update of GUID,ImageGUID,UserGUID on idUserFavorite
begin
select case when
((select 1 from idCatalogItem where
GUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idUserUsage
before insert on idUserUsage
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idUserUsage
before update of GUID,UserGUID on idUserUsage
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idUserUser
before insert on idUserUser
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID2
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idUserUser
before update of GUID,UserGUID,UserGUID2 on idUserUser
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID2
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bd_idImageBox
before delete on idImageBox
begin
delete from idImageBoxUsage where
BoxGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageBoxUsage
before insert on idImageBoxUsage
begin
select case when
((select 1 from idImageBox where
GUID = new.BoxGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageBox'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idImageBoxUsage
before update of BoxGUID,UserGUID on idImageBoxUsage
begin
select case when
((select 1 from idImageBox where
GUID = new.BoxGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageBox'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idImageModel
before insert on idImageModel
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idImageModel
before update of GUID,UserGUID on idImageModel
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bd_idImageModel
before delete on idImageModel
begin
delete from idImageGallery where
ModelGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageGallery
before insert on idImageGallery
begin
select case when
((select 1 from idImageModel where
GUID = new.ModelGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageModel'
)
end;
end;
CREATE TRIGGER t_bu_idImageGallery
before update of GUID,ModelGUID on idImageGallery
begin
select case when
((select 1 from idImageModel where
GUID = new.ModelGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageModel'
)
end;
end;
CREATE TRIGGER t_bd_idImageGallery
before delete on idImageGallery
begin
delete from idImageCollection where
GalleryGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageCollection
before insert on idImageCollection
begin
select case when
((select 1 from idImageGallery where
GUID = new.GalleryGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageGallery'
)
end;
end;
CREATE TRIGGER t_bu_idImageCollection
before update of GalleryGUID,GUID on idImageCollection
begin
select case when
((select 1 from idImageGallery where
GUID = new.GalleryGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageGallery'
)
end;
end;
CREATE TRIGGER t_bd_idImageCollection
before delete on idImageCollection
begin
delete from idImageCollectionItem where
GUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageCollectionItem
before insert on idImageCollectionItem
begin
select case when
((select 1 from idImageCollection where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageCollection'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bu_idImageCollectionItem
before update of GUID,ItemGUID on idImageCollectionItem
begin
select case when
((select 1 from idImageCollection where
GUID = new.GUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageCollection'
)
end;
select case when
((select 1 from idCatalogItem where
GUID = new.ItemGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
end;
CREATE TRIGGER t_bd_idImageCollectionItem
before delete on idImageCollectionItem
begin
delete from idImageScore where
ContestGUID = old.GUID
and ImageGUID = old.ItemGUID
;
end;
CREATE TRIGGER t_bi_idImageScore
before insert on idImageScore
begin
select case when
((select 1 from idImageCollectionItem where
GUID = new.ContestGUID
and ItemGUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageCollectionItem'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idImageScore
before update of ContestGUID,GUID,ImageGUID,UserGUID on idImageScore
begin
select case when
((select 1 from idImageCollectionItem where
GUID = new.ContestGUID
and ItemGUID = new.ImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idImageCollectionItem'
)
end;
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idPropRelation
before insert on idPropRelation
begin
select case when
((select 1 from idProp where
GUID = new.ParentGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idProp where
GUID = new.ChildGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER t_bu_idPropRelation
before update of ChildGUID,GUID,ParentGUID on idPropRelation
begin
select case when
((select 1 from idProp where
GUID = new.ParentGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
select case when
((select 1 from idProp where
GUID = new.ChildGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER t_bi_idProp
before insert on idProp
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bu_idProp
before update of GUID,ParentGUID,UserGUID on idProp
begin
select case when
((select 1 from idUser where
GUID = new.UserGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idUser'
)
end;
end;
CREATE TRIGGER t_bi_idImageArea
before insert on idImageArea
begin
select case when
((select 1 from idProp where
GUID = new.PropGUID
) is null)
and (IfNull(new.PropGUID, '') <> '')
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER t_bu_idImageArea
before update of GUID,ImageGUID,PropGUID on idImageArea
begin
select case when
((select 1 from idProp where
GUID = new.PropGUID
) is null)
and (IfNull(new.PropGUID, '') <> '')
then raise (abort,
'Foreign key violation: no record in idProp'
)
end;
end;
CREATE TRIGGER [t_bu_idCatalogItem] BEFORE UPDATE OF [GUID], [PathGUID], [UserGUID] ON [idCatalogItem]
BEGIN
SELECT CASE WHEN ((SELECT 1
FROM [idUser]
WHERE [GUID] = [new].[UserGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idUser') END;
SELECT CASE WHEN ((SELECT 1
FROM [idFilePath]
WHERE [GUID] = [new].[PathGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idFilePath') END;
END;
CREATE TRIGGER [t_bi_idCatalogItem] BEFORE INSERT ON [idCatalogItem]
BEGIN
SELECT CASE WHEN ((SELECT 1
FROM [idUser]
WHERE [GUID] = [new].[UserGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idUser') END;
SELECT CASE WHEN ((SELECT 1
FROM [idFilePath]
WHERE [GUID] = [new].[PathGUID]) IS NULL) THEN RAISE(ABORT, 'Foreign key violation: no record in idFilePath') END;
END;
CREATE TRIGGER [t_bd_idCatalogItem] BEFORE DELETE ON [idCatalogItem]
BEGIN
DELETE FROM
[idCatalogItemDefinition]
WHERE
[CatalogItemGUID] = [old].[GUID];
DELETE FROM
[idImageCard]
WHERE
[ImageGUID] = [old].[GUID];
DELETE FROM
[idImageSubscription]
WHERE
[ImageGUID] = [old].[GUID];
DELETE FROM
[idImageIPTC]
WHERE
[ImageGUID] = [old].[GUID];
DELETE FROM
[idImageVersion]
WHERE
[MainImageGUID] = [old].[GUID];
DELETE FROM
[idImageComment]
WHERE
[ImageGUID] = [old].[GUID];
DELETE FROM
[idUserFavorite]
WHERE
[ImageGUID] = [old].[GUID];
DELETE FROM
[idImageCollectionItem]
WHERE
[ItemGUID] = [old].[GUID];
DELETE FROM
[idImageArea]
WHERE
[ImageGUID] = [old].[GUID];
END;
CREATE TRIGGER t_bd_idImageVersion
before delete on idImageVersion
begin
delete from idVersionPlaceHolder where
ItemGUID = old.GUID
;
delete from idImageArea where
ImageGUID = old.GUID
;
end;
CREATE TRIGGER t_bi_idImageVersion
before insert on idImageVersion
begin
select case when
((select 1 from idCatalogItem where
GUID = new.MainImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
select case when
((select 1 from idFilePath where
GUID = new.PathGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idFilePath'
)
end;
end;
CREATE TRIGGER t_bu_idImageVersion
before update of GUID,MainImageGUID,PathGUID on idImageVersion
begin
select case when
((select 1 from idCatalogItem where
GUID = new.MainImageGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idCatalogItem'
)
end;
select case when
((select 1 from idFilePath where
GUID = new.PathGUID
) is null)
then raise (abort,
'Foreign key violation: no record in idFilePath'
)
end;
end;
CREATE TRIGGER t_bd_idPropCategory
before delete on idPropCategory
begin
delete from idProp where ParentGUID = old.GUID;
end;
CREATE TRIGGER t_au_idFilePath
after update on idFilePath
for each row
when (
(old.ParentGUID <> new.ParentGUID) or
(old.FolderName <> new.FolderName)
) and (
exists (select 1 from idCache_FilePath where FilePathGUID = new.GUID)
)
begin
-- update
update idCache_FilePath
set (idLevel, GUID, ParentGUID, FilePath, RootGUID) =
(
select idLevel, GUID, ParentGUID, FilePath, RootGUID
from (
with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as
(
select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID
from idFilePath fp
inner join idMediumInfo m on m.GUID = fp.MediumGUID
where fp.GUID = idCache_FilePath.FilePathGUID
union all
select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID
from higherparent hp
inner join idFilePath fp on fp.GUID = hp.ParentGUID
inner join idMediumInfo m on m.GUID = fp.MediumGUID
)
select *
from higherparent
order by idLevel
limit 1
)
)
where
FilePathGUID in (
select GUID
from (
with recursive lowerparent(FilePathGUID, idLevel, GUID, ParentGUID) as
(
select fp.GUID, 0, fp.GUID, fp.ParentGUID
from idFilePath fp
where fp.GUID = new.GUID
union all
select lp.FilePathGUID, lp.idLevel+1, fp.GUID, fp.ParentGUID
from idFilePath fp, lowerparent lp
where fp.ParentGUID = lp.GUID
)
select *
from lowerparent
)
);
end
;
CREATE TRIGGER t_au2_idFilePath
after update on idFilePath
for each row
when not exists (select 1 from idCache_FilePath where FilePathGUID = new.GUID)
begin
-- insert
insert into idCache_FilePath
(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID)
select FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID
from (
with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as
(
select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID
from idFilePath fp
inner join idMediumInfo m on m.GUID = fp.MediumGUID
where fp.GUID = new.GUID
union all
select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID
from higherparent hp
inner join idFilePath fp on fp.GUID = hp.ParentGUID
inner join idMediumInfo m on m.GUID = fp.MediumGUID
)
select *
from higherparent
order by idLevel
limit 1
);
end
;
CREATE TRIGGER t_ai_idFilePath
after insert on idFilePath
for each row
when not exists (select 1 from idCache_FilePath where FilePathGUID = new.GUID)
begin
-- insert
insert into idCache_FilePath
(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID)
select FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID
from (
with recursive higherparent(FilePathGUID, idLevel, GUID, ParentGUID, FilePath, RootGUID) as
(
select fp.GUID, 0, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator as FilePath, fp.ParentGUID
from idFilePath fp
inner join idMediumInfo m on m.GUID = fp.MediumGUID
where fp.GUID = new.GUID
union all
select hp.FilePathGUID, hp.idLevel-1, fp.GUID, fp.ParentGUID, fp.FolderName || m.PathSeparator || hp.FilePath as FilePath, hp.ParentGUID
from higherparent hp
inner join idFilePath fp on fp.GUID = hp.ParentGUID
inner join idMediumInfo m on m.GUID = fp.MediumGUID
)
select *
from higherparent
order by idLevel
limit 1
);
end
;
CREATE TRIGGER t_bd_idFilePath
before delete on idFilePath
for each row
begin
delete from idCatalogItem
where PathGUID = old.GUID;
delete from idImageVersion
where PathGUID = old.GUID;
delete from idCache_FilePath
where FilePathGUID in (
select GUID
from (
with recursive lowerparent(FilePathGUID, idLevel, GUID, ParentGUID) as
(
select fp.GUID, 0, fp.GUID, fp.ParentGUID
from idFilePath fp
where fp.GUID = old.GUID
union all
select lp.FilePathGUID, lp.idLevel+1, fp.GUID, fp.ParentGUID
from idFilePath fp, lowerparent lp
where fp.ParentGUID = lp.GUID
)
select *
from lowerparent
)
);
end;
CREATE TRIGGER t_ai_idProp
after insert on idProp
for each row
when not exists (select 1 from idCache_Prop where PropGUID = new.GUID)
begin
-- insert
insert
into idCache_Prop (PropGUID, CategoryGUID)
values (
new.GUID,
(
WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS
(
SELECT 0 AS idlevel,
px.guid,
px.parentguid
FROM idprop px
WHERE px.guid = new.GUID
UNION ALL
SELECT hp_1.idlevel - 1 AS idlevel,
hp_1.guid,
px.parentguid
FROM idprop px,
higherparent hp_1
WHERE px.guid = hp_1.parentguid
)
SELECT hp.parentguid
FROM higherparent hp
WHERE hp.guid = new.GUID
ORDER BY hp.idlevel
LIMIT 1
)
);
end
;
CREATE TRIGGER t_au_idProp
after update on idProp
for each row
when (
(old.ParentGUID <> new.ParentGUID)
) and (
exists (select 1 from idCache_Prop where PropGUID = new.GUID)
)
begin
-- update
update idCache_Prop
set PropGUID = new.GUID,
CategoryGUID = (
WITH RECURSIVE higherparent(idlevel, guid, parentguid) AS
(
SELECT 0 AS idlevel,
px.guid,
px.parentguid
FROM idprop px
WHERE px.guid = new.GUID
UNION ALL
SELECT hp_1.idlevel - 1 AS idlevel,
hp_1.guid,
px.parentguid
FROM idprop px,
higherparent hp_1
WHERE px.guid = hp_1.parentguid
)
SELECT hp.parentguid
FROM higherparent hp
WHERE hp.guid = new.GUID
ORDER BY hp.idlevel
LIMIT 1
)
where
PropGUID = old.GUID;
end
;
CREATE TRIGGER t_bd_idProp
before delete on idProp
begin
delete from idPropGroupContent where PropGUID = old.GUID;
delete from idCatalogItemDefinition where GUID = old.GUID;
delete from idUserProp where GUID = old.GUID;
delete from idPropRelation where ParentGUID = old.GUID;
delete from idPropRelation where ChildGUID = old.GUID;
delete from idImageArea where PropGUID = old.GUID;
delete
from idCache_Prop
where PropGUID = old.GUID;
end;