22 lines
1.5 KiB
PL/PgSQL
22 lines
1.5 KiB
PL/PgSQL
SET search_path TO atlas, public;
|
|
|
|
-- 删除保护也落在数据库，禁止直接更新绕过 SKU、头像、水印和活跃任务的引用校验。
|
|
CREATE FUNCTION guard_media_asset_deletion() RETURNS trigger LANGUAGE plpgsql SET search_path = atlas, public AS $$
|
|
BEGIN
|
|
IF OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL THEN
|
|
PERFORM 1 FROM media_assets WHERE id = OLD.id FOR UPDATE;
|
|
IF EXISTS (SELECT 1 FROM product_variant_listing_media WHERE asset_id = OLD.id)
|
|
OR EXISTS (SELECT 1 FROM watermark_template_versions WHERE image_asset_id = OLD.id)
|
|
OR EXISTS (SELECT 1 FROM users WHERE avatar_asset_id = OLD.id)
|
|
OR EXISTS (SELECT 1 FROM media_ai_edits WHERE status IN ('queued','running') AND (source_asset_id = OLD.id OR OLD.id = ANY(reference_asset_ids)))
|
|
OR EXISTS (SELECT 1 FROM media_aspect_ratio_derivatives WHERE status IN ('queued','running') AND source_asset_id = OLD.id)
|
|
OR EXISTS (SELECT 1 FROM media_watermark_derivatives WHERE status IN ('queued','running') AND source_asset_id = OLD.id)
|
|
OR EXISTS (SELECT 1 FROM product_image_suite_references r JOIN product_image_suites s ON s.id = r.suite_id WHERE r.asset_id = OLD.id AND s.status IN ('queued','running')) THEN
|
|
RAISE EXCEPTION 'media asset is in use' USING ERRCODE = '23514';
|
|
END IF;
|
|
END IF;
|
|
RETURN NEW;
|
|
END $$;
|
|
CREATE TRIGGER guard_media_asset_deletion BEFORE UPDATE OF deleted_at ON media_assets
|
|
FOR EACH ROW EXECUTE FUNCTION guard_media_asset_deletion();
|