Database Hooks & Triggers

<h2 id="database-hooks--triggers" class="position-relative d-flex align-items-center group"> Database Hooks &amp; Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="database-hooks--triggers" aria-haspopup="dialog" aria-label="Share link: Database Hooks &amp; Triggers"> Share link </button> </h2><div id="headingShareModal" class="heading-share-modal" role="dialog" aria-modal="true" aria-labelledby="headingShareTitle" hidden> <div class="hsm-dialog" role="document"> <div class="hsm-header"> <h2 id="headingShareTitle" class="h6 mb-0 fw-bold">Share this section</h2> <button type="button" class="hsm-close" aria-label="Close"> </button> </div> <div class="hsm-body"> <label for="headingShareInput" class="form-label small text-muted mb-1 text-uppercase fw-bold" style="font-size: 0.7rem; letter-spacing: 0.5px;">Permalink</label> <div class="input-group mb-4 hsm-url-group"> <input id="headingShareInput" type="text" class="form-control font-monospace" readonly aria-readonly="true" style="font-size: 0.85rem;" /> <button class="btn btn-primary hsm-copy" type="button" aria-label="Copy" title="Copy"> </button> </div> <div class="small fw-bold mb-2 text-muted text-uppercase" style="font-size: 0.7rem; letter-spacing: 0.5px;">Share via</div> <div class="hsm-share-grid"> <a id="share-twitter" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Twitter </a> <a id="share-linkedin" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> LinkedIn </a> <a id="share-facebook" class="btn btn-outline-secondary w-100" target="_blank" rel="noopener noreferrer"> Facebook </a> </div> </div> </div> </div> <style> .heading-share-modal { position: fixed; inset: 0; display: flex; justify-content: center; align-items: center; background: rgba(0, 0, 0, 0.6); z-index: 1050; padding: 1rem; backdrop-filter: blur(4px); -webkit-backdrop-filter: blur(4px); } .heading-share-modal[hidden] { display: none !important; } .hsm-dialog { max-width: 420px; width: 100%; background: var(--bs-body-bg, #fff); color: var(--bs-body-color, #212529); border: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); border-radius: 1rem; box-shadow: 0 25px 50px -12px rgba(0, 0, 0, 0.25); overflow: hidden; animation: hsm-fade-in 0.2s ease-out; } @keyframes hsm-fade-in { from { opacity: 0; transform: scale(0.95); } to { opacity: 1; transform: scale(1); } } [data-bs-theme="dark"] .hsm-dialog { background: #1e293b; border-color: rgba(255,255,255,0.1); color: #f8f9fa; } .hsm-header { display: flex; justify-content: space-between; align-items: center; padding: 1rem 1.5rem; border-bottom: 1px solid var(--bs-border-color, rgba(0,0,0,0.1)); background: rgba(0,0,0,0.02); } [data-bs-theme="dark"] .hsm-header { background: rgba(255,255,255,0.02); border-color: rgba(255,255,255,0.1); } .hsm-close { background: transparent; border: none; color: inherit; opacity: 0.5; padding: 0.25rem 0.5rem; border-radius: 0.25rem; font-size: 1.2rem; line-height: 1; transition: opacity 0.2s; } .hsm-close:hover { opacity: 1; } .hsm-body { padding: 1.5rem; } .hsm-url-group { display: flex !important; align-items: stretch; } .hsm-url-group .form-control { flex: 1; min-width: 0; margin: 0; background: var(--bs-secondary-bg, #f8f9fa); border-color: var(--bs-border-color, #dee2e6); border-top-right-radius: 0; border-bottom-right-radius: 0; height: 42px; } .hsm-url-group .btn { flex: 0 0 auto; margin: 0; margin-left: -1px; border-top-left-radius: 0; border-bottom-left-radius: 0; height: 42px; display: flex; align-items: center; justify-content: center; padding: 0 1.25rem; z-index: 2; } [data-bs-theme="dark"] .hsm-url-group .form-control { background: #0f172a; border-color: #334155; color: #e2e8f0; } .hsm-share-grid { display: flex; flex-direction: column; gap: 0.5rem; } .hsm-share-grid .btn { display: flex; align-items: center; justify-content: center; font-size: 0.9rem; padding: 0.6rem; border-color: var(--bs-border-color); width: 100%; } [data-bs-theme="dark"] .hsm-share-grid .btn { color: #e2e8f0; border-color: #475569; } [data-bs-theme="dark"] .hsm-share-grid .btn:hover { background: #334155; border-color: #cbd5e1; } </style> <script> (function(){ const modal = document.getElementById('headingShareModal'); if(!modal) return; const input = modal.querySelector('#headingShareInput'); const copyBtn = modal.querySelector('.hsm-copy'); const twitter = modal.querySelector('#share-twitter'); const linkedin = modal.querySelector('#share-linkedin'); const facebook = modal.querySelector('#share-facebook'); const closeBtn = modal.querySelector('.hsm-close'); let lastFocus=null; let trapBound=false; function buildUrl(id){ return window.location.origin + window.location.pathname + '#' + id; } function isOpen(){ return !modal.hasAttribute('hidden'); } function hydrate(id){ const url=buildUrl(id); input.value=url; const enc=encodeURIComponent(url); const text=encodeURIComponent(document.title); if(twitter) twitter.href=`https://twitter.com/intent/tweet?url=${enc}&text=${text}`; if(linkedin) linkedin.href=`https://www.linkedin.com/sharing/share-offsite/?url=${enc}`; if(facebook) facebook.href=`https://www.facebook.com/sharer/sharer.php?u=${enc}`; } function openModal(id){ lastFocus=document.activeElement; hydrate(id); if(!isOpen()){ modal.removeAttribute('hidden'); } requestAnimationFrame(()=>{ input.focus(); }); trapFocus(); } function closeModal(){ if(!isOpen()) return; modal.setAttribute('hidden',''); if(lastFocus && typeof lastFocus.focus==='function') lastFocus.focus(); } function copyCurrent(){ try{ navigator.clipboard.writeText(input.value).then(()=>feedback(true),()=>fallback()); } catch(e){ fallback(); } } function fallback(){ input.select(); try{ document.execCommand('copy'); feedback(true);}catch(e){ feedback(false);} } function feedback(ok){ if(!copyBtn) return; const icon=copyBtn.querySelector('i'); if(!icon) return; const prev=copyBtn.getAttribute('data-prev')||icon.className; if(!copyBtn.getAttribute('data-prev')) copyBtn.setAttribute('data-prev',prev); icon.className= ok ? 'fa-duotone fa-clipboard-check':'fa-duotone fa-circle-exclamation'; setTimeout(()=>{ icon.className=prev; },1800); } function handleShareClick(e){ e.preventDefault(); const btn=e.currentTarget; const id=btn.getAttribute('data-share-target'); if(id) openModal(id); } function bindShareButtons(){ document.querySelectorAll('.h-share').forEach(btn=>{ if(!btn.dataset.hShareBound){ btn.addEventListener('click', handleShareClick); btn.dataset.hShareBound='1'; } }); } bindShareButtons(); if(document.readyState==='loading'){ document.addEventListener('DOMContentLoaded', bindShareButtons); } else { requestAnimationFrame(bindShareButtons); } document.addEventListener('click', function(e){ const shareBtn=e.target.closest && e.target.closest('.h-share'); if(shareBtn && !shareBtn.dataset.hShareBound){ handleShareClick.call(shareBtn, e); } }, true); document.addEventListener('click', e=>{ if(e.target===modal) closeModal(); if(e.target.closest && e.target.closest('.hsm-close')){ e.preventDefault(); closeModal(); } if(copyBtn && (e.target===copyBtn || (e.target.closest && e.target.closest('.hsm-copy')))) { e.preventDefault(); copyCurrent(); } }); document.addEventListener('keydown', e=>{ if(e.key==='Escape' && isOpen()) closeModal(); }); function trapFocus(){ if(trapBound) return; trapBound=true; modal.addEventListener('keydown', f=>{ if(f.key==='Tab' && isOpen()){ const focusable=[...modal.querySelectorAll('a[href],button,input,textarea,select,[tabindex]:not([tabindex="-1"])')].filter(el=>!el.hasAttribute('disabled')); if(!focusable.length) return; const first=focusable[0]; const last=focusable[focusable.length-1]; if(f.shiftKey && document.activeElement===first){ f.preventDefault(); last.focus(); } else if(!f.shiftKey && document.activeElement===last){ f.preventDefault(); first.focus(); } } }); } if(closeBtn) closeBtn.addEventListener('click', e=>{ e.preventDefault(); closeModal(); }); })(); </script>Database hooks and triggers are mechanisms that automatically execute custom logic in response to specific database events. Geode’s hooks and triggers enable reactive data management, constraint enforcement, and workflow automation without requiring application-level code. <h3 id="trigger-fundamentals" class="position-relative d-flex align-items-center group"> Trigger Fundamentals <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-fundamentals" aria-haspopup="dialog" aria-label="Share link: Trigger Fundamentals"> Share link </button> </h3> <h4 id="what-are-triggers" class="position-relative d-flex align-items-center group"> What are Triggers? <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="what-are-triggers" aria-haspopup="dialog" aria-label="Share link: What are Triggers?"> Share link </button> </h4>Triggers are stored procedures that execute automatically when specific events occur: BEFORE Triggers - Execute before the operation AFTER Triggers - Execute after the operation INSTEAD OF Triggers - Replace the original operation <h4 id="trigger-events" class="position-relative d-flex align-items-center group"> Trigger Events <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-events" aria-haspopup="dialog" aria-label="Share link: Trigger Events"> Share link </button> </h4>Triggers respond to DML operations: <ul> <li>INSERT - New nodes or edges created</li> <li>UPDATE - Properties modified</li> <li>DELETE - Nodes or edges removed</li> </ul> <h3 id="creating-triggers" class="position-relative d-flex align-items-center group"> Creating Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="creating-triggers" aria-haspopup="dialog" aria-label="Share link: Creating Triggers"> Share link </button> </h3> <h4 id="basic-trigger-syntax" class="position-relative d-flex align-items-center group"> Basic Trigger Syntax <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="basic-trigger-syntax" aria-haspopup="dialog" aria-label="Share link: Basic Trigger Syntax"> Share link </button> </h4><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- After insert trigger CREATE TRIGGER audit_new_users AFTER INSERT ON Person FOR EACH ROW EXECUTE GQL INSERT (a:AuditLog { action: 'user_created', user_id: NEW.id, timestamp: NOW(), details: NEW.properties }); </code></pre></div> <h4 id="before-update-trigger" class="position-relative d-flex align-items-center group"> Before Update Trigger <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="before-update-trigger" aria-haspopup="dialog" aria-label="Share link: Before Update Trigger"> Share link </button> </h4>Validate or modify data before update: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Prevent negative balance CREATE TRIGGER check_balance BEFORE UPDATE ON Account FOR EACH ROW WHEN (NEW.balance < 0) EXECUTE GQL SIGNAL 'Insufficient funds'; -- Auto-update modification time CREATE TRIGGER update_modified_time BEFORE UPDATE ON Person FOR EACH ROW EXECUTE GQL SET NEW.modified_at = NOW(); </code></pre></div> <h4 id="after-delete-trigger" class="position-relative d-flex align-items-center group"> After Delete Trigger <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="after-delete-trigger" aria-haspopup="dialog" aria-label="Share link: After Delete Trigger"> Share link </button> </h4>Cascade operations after deletion: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Archive deleted users CREATE TRIGGER archive_deleted_users AFTER DELETE ON Person FOR EACH ROW EXECUTE GQL INSERT (a:ArchivedPerson { original_id: OLD.id, data: OLD.properties, deleted_at: NOW(), deleted_by: CURRENT_USER() }); -- Clean up orphaned data CREATE TRIGGER cleanup_user_data AFTER DELETE ON Person FOR EACH ROW EXECUTE GQL DELETE FROM Post WHERE author_id = OLD.id; DELETE FROM Comment WHERE author_id = OLD.id; </code></pre></div> <h3 id="hook-types" class="position-relative d-flex align-items-center group"> Hook Types <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hook-types" aria-haspopup="dialog" aria-label="Share link: Hook Types"> Share link </button> </h3> <h4 id="lifecycle-hooks" class="position-relative d-flex align-items-center group"> Lifecycle Hooks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="lifecycle-hooks" aria-haspopup="dialog" aria-label="Share link: Lifecycle Hooks"> Share link </button> </h4>Execute at specific points in entity lifecycle: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Before creation CREATE HOOK before_create_person BEFORE INSERT ON Person EXECUTE FUNCTION validate_user_data(NEW); -- After creation CREATE HOOK after_create_person AFTER INSERT ON Person EXECUTE FUNCTION send_welcome_email(NEW.email); -- Before update CREATE HOOK before_update_person BEFORE UPDATE ON Person EXECUTE FUNCTION validate_changes(OLD, NEW); -- After update CREATE HOOK after_update_person AFTER UPDATE ON Person EXECUTE FUNCTION notify_profile_change(NEW); </code></pre></div> <h4 id="validation-hooks" class="position-relative d-flex align-items-center group"> Validation Hooks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="validation-hooks" aria-haspopup="dialog" aria-label="Share link: Validation Hooks"> Share link </button> </h4>Enforce business rules: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Age validation CREATE TRIGGER validate_age BEFORE INSERT OR UPDATE ON Person FOR EACH ROW WHEN (NEW.age < 0 OR NEW.age > 150) EXECUTE GQL SIGNAL 'Invalid age value'; -- Email uniqueness CREATE TRIGGER ensure_unique_email BEFORE INSERT ON Person FOR EACH ROW WHEN EXISTS (SELECT 1 FROM Person WHERE email = NEW.email) EXECUTE GQL SIGNAL 'Email already in use'; -- Required fields CREATE TRIGGER check_required_fields BEFORE INSERT ON Person FOR EACH ROW WHEN (NEW.name IS NULL OR NEW.email IS NULL) EXECUTE GQL SIGNAL 'Required fields missing'; </code></pre></div> <h4 id="computed-properties" class="position-relative d-flex align-items-center group"> Computed Properties <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="computed-properties" aria-haspopup="dialog" aria-label="Share link: Computed Properties"> Share link </button> </h4>Auto-calculate derived values: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Full name from first and last CREATE TRIGGER compute_full_name BEFORE INSERT OR UPDATE ON Person FOR EACH ROW EXECUTE GQL SET NEW.full_name = NEW.first_name || ' ' || NEW.last_name; -- Age from birthdate CREATE TRIGGER compute_age BEFORE INSERT OR UPDATE ON Person FOR EACH ROW EXECUTE GQL SET NEW.age = YEAR(NOW()) - YEAR(NEW.birthdate); -- Order total CREATE TRIGGER compute_order_total BEFORE INSERT OR UPDATE ON Order FOR EACH ROW EXECUTE GQL SET NEW.total = ( SELECT SUM(quantity * price) FROM OrderItem WHERE order_id = NEW.id ); </code></pre></div> <h4 id="audit-logging" class="position-relative d-flex align-items-center group"> Audit Logging <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="audit-logging" aria-haspopup="dialog" aria-label="Share link: Audit Logging"> Share link </button> </h4>Track all data changes: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Comprehensive audit trail CREATE TRIGGER audit_all_changes AFTER INSERT OR UPDATE OR DELETE ON Person FOR EACH ROW EXECUTE GQL INSERT (log:AuditLog { table_name: 'Person', operation: CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' WHEN DELETING THEN 'DELETE' END, record_id: COALESCE(NEW.id, OLD.id), old_value: OLD.properties, new_value: NEW.properties, changed_by: CURRENT_USER(), changed_at: NOW(), ip_address: CURRENT_IP() }); </code></pre></div> <h3 id="advanced-patterns" class="position-relative d-flex align-items-center group"> Advanced Patterns <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="advanced-patterns" aria-haspopup="dialog" aria-label="Share link: Advanced Patterns"> Share link </button> </h3> <h4 id="conditional-triggers" class="position-relative d-flex align-items-center group"> Conditional Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="conditional-triggers" aria-haspopup="dialog" aria-label="Share link: Conditional Triggers"> Share link </button> </h4>Execute only when conditions met: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Only trigger for significant changes CREATE TRIGGER notify_major_update AFTER UPDATE ON Account FOR EACH ROW WHEN (ABS(NEW.balance - OLD.balance) > 10000) EXECUTE GQL INSERT (n:Notification { type: 'large_transaction', account_id: NEW.id, old_balance: OLD.balance, new_balance: NEW.balance, difference: NEW.balance - OLD.balance, timestamp: NOW() }); -- Status-based triggers CREATE TRIGGER handle_order_completion AFTER UPDATE ON Order FOR EACH ROW WHEN (OLD.status != 'completed' AND NEW.status = 'completed') EXECUTE GQL INSERT (e:Event {type: 'order_completed', order_id: NEW.id}); UPDATE Customer SET total_orders = total_orders + 1 WHERE id = NEW.customer_id; </code></pre></div> <h4 id="multi-table-triggers" class="position-relative d-flex align-items-center group"> Multi-Table Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="multi-table-triggers" aria-haspopup="dialog" aria-label="Share link: Multi-Table Triggers"> Share link </button> </h4>Affect multiple tables: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Cascade inventory update CREATE TRIGGER update_inventory AFTER INSERT ON OrderItem FOR EACH ROW EXECUTE GQL UPDATE Product SET stock_quantity = stock_quantity - NEW.quantity, last_ordered = NOW() WHERE id = NEW.product_id; INSERT (h:InventoryHistory { product_id: NEW.product_id, change: -NEW.quantity, reason: 'order_placed', order_id: NEW.order_id, timestamp: NOW() }); </code></pre></div> <h4 id="recursive-triggers" class="position-relative d-flex align-items-center group"> Recursive Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="recursive-triggers" aria-haspopup="dialog" aria-label="Share link: Recursive Triggers"> Share link </button> </h4>Triggers that may trigger themselves: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Set recursion limit SET max_trigger_recursion = 10; -- Hierarchical update CREATE TRIGGER propagate_category_update AFTER UPDATE ON Category FOR EACH ROW EXECUTE GQL UPDATE Category SET parent_updated_at = NEW.updated_at WHERE parent_id = NEW.id; </code></pre></div> <h3 id="trigger-management" class="position-relative d-flex align-items-center group"> Trigger Management <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-management" aria-haspopup="dialog" aria-label="Share link: Trigger Management"> Share link </button> </h3> <h4 id="enablingdisabling-triggers" class="position-relative d-flex align-items-center group"> Enabling/Disabling Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="enablingdisabling-triggers" aria-haspopup="dialog" aria-label="Share link: Enabling/Disabling Triggers"> Share link </button> </h4>Control trigger execution: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Disable trigger ALTER TRIGGER audit_new_users DISABLE; -- Enable trigger ALTER TRIGGER audit_new_users ENABLE; -- Disable all triggers on table ALTER TABLE Person DISABLE ALL TRIGGERS; -- Enable all triggers on table ALTER TABLE Person ENABLE ALL TRIGGERS; </code></pre></div> <h4 id="dropping-triggers" class="position-relative d-flex align-items-center group"> Dropping Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="dropping-triggers" aria-haspopup="dialog" aria-label="Share link: Dropping Triggers"> Share link </button> </h4>Remove triggers: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Drop specific trigger DROP TRIGGER IF EXISTS audit_new_users; -- Drop all triggers on table DROP ALL TRIGGERS ON Person; </code></pre></div> <h4 id="trigger-inspection" class="position-relative d-flex align-items-center group"> Trigger Inspection <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-inspection" aria-haspopup="dialog" aria-label="Share link: Trigger Inspection"> Share link </button> </h4>Query trigger metadata: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- List all triggers SELECT trigger_name, table_name, event, timing, enabled FROM SYSTEM.triggers; -- Get trigger definition SELECT trigger_name, definition FROM SYSTEM.triggers WHERE trigger_name = 'audit_new_users'; </code></pre></div> <h3 id="performance-considerations" class="position-relative d-flex align-items-center group"> Performance Considerations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="performance-considerations" aria-haspopup="dialog" aria-label="Share link: Performance Considerations"> Share link </button> </h3> <h4 id="trigger-overhead" class="position-relative d-flex align-items-center group"> Trigger Overhead <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-overhead" aria-haspopup="dialog" aria-label="Share link: Trigger Overhead"> Share link </button> </h4>Minimize impact on write performance: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Batch audit logging instead of per-row CREATE TRIGGER batch_audit AFTER INSERT ON Person FOR EACH STATEMENT EXECUTE GQL INSERT INTO audit_log SELECT 'INSERT', NEW.id, NEW.properties, NOW() FROM NEW; </code></pre></div> <h4 id="async-triggers" class="position-relative d-flex align-items-center group"> Async Triggers <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="async-triggers" aria-haspopup="dialog" aria-label="Share link: Async Triggers"> Share link </button> </h4>Defer non-critical work: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Queue for async processing CREATE TRIGGER queue_email AFTER INSERT ON Person FOR EACH ROW EXECUTE GQL INSERT INTO email_queue ( type: 'welcome', recipient: NEW.email, data: NEW.properties, queued_at: NOW() ); </code></pre></div> <h4 id="trigger-ordering" class="position-relative d-flex align-items-center group"> Trigger Ordering <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-ordering" aria-haspopup="dialog" aria-label="Share link: Trigger Ordering"> Share link </button> </h4>Control execution order: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Explicit ordering CREATE TRIGGER validate_first BEFORE INSERT ON Person ORDER 1 EXECUTE GQL ...; CREATE TRIGGER audit_second BEFORE INSERT ON Person ORDER 2 EXECUTE GQL ...; </code></pre></div> <h3 id="best-practices" class="position-relative d-flex align-items-center group"> Best Practices <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="best-practices" aria-haspopup="dialog" aria-label="Share link: Best Practices"> Share link </button> </h3> <h4 id="trigger-design" class="position-relative d-flex align-items-center group"> Trigger Design <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-design" aria-haspopup="dialog" aria-label="Share link: Trigger Design"> Share link </button> </h4><ul> <li>Keep triggers simple and focused</li> <li>Avoid complex business logic</li> <li>Document trigger purpose</li> <li>Test thoroughly</li> <li>Monitor performance impact</li> </ul> <h4 id="error-handling" class="position-relative d-flex align-items-center group"> Error Handling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="error-handling" aria-haspopup="dialog" aria-label="Share link: Error Handling"> Share link </button> </h4><ul> <li>Use SIGNAL for validation errors</li> <li>Provide meaningful error messages</li> <li>Log errors for debugging</li> <li>Consider compensation logic</li> </ul> <h4 id="security" class="position-relative d-flex align-items-center group"> Security <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="security" aria-haspopup="dialog" aria-label="Share link: Security"> Share link </button> </h4><ul> <li>Validate all inputs</li> <li>Check permissions</li> <li>Prevent SQL injection</li> <li>Audit trigger execution</li> <li>Limit trigger recursion</li> </ul> <h3 id="related-topics" class="position-relative d-flex align-items-center group"> Related Topics <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="related-topics" aria-haspopup="dialog" aria-label="Share link: Related Topics"> Share link </button> </h3><ul> <li><a href="/tags/events" >Events</a> - Event-driven architecture</li> <li><a href="/tags/validation" >Validation</a> - Data validation</li> <li><a href="/tags/constraints" >Constraints</a> - Schema constraints</li> </ul> <h3 id="real-world-hook-implementations" class="position-relative d-flex align-items-center group"> Real-World Hook Implementations <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="real-world-hook-implementations" aria-haspopup="dialog" aria-label="Share link: Real-World Hook Implementations"> Share link </button> </h3> <h4 id="multi-tenancy-enforcement" class="position-relative d-flex align-items-center group"> Multi-Tenancy Enforcement <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="multi-tenancy-enforcement" aria-haspopup="dialog" aria-label="Share link: Multi-Tenancy Enforcement"> Share link </button> </h4>Automatically add tenant context to all data: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Inject tenant_id on all creates CREATE TRIGGER enforce_tenancy BEFORE INSERT ON Person, Company, Product FOR EACH ROW EXECUTE GQL SET NEW.tenant_id = CURRENT_TENANT_ID(), NEW.created_by = CURRENT_USER(); -- Prevent cross-tenant access CREATE TRIGGER validate_tenant_access BEFORE UPDATE OR DELETE ON Person, Company, Product FOR EACH ROW WHEN (OLD.tenant_id != CURRENT_TENANT_ID()) EXECUTE GQL SIGNAL 'Access denied: cross-tenant operation not allowed'; </code></pre></div> <h4 id="data-retention-policy" class="position-relative d-flex align-items-center group"> Data Retention Policy <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="data-retention-policy" aria-haspopup="dialog" aria-label="Share link: Data Retention Policy"> Share link </button> </h4>Automatically archive or delete old data: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Archive records older than 7 years CREATE TRIGGER enforce_retention_policy AFTER UPDATE ON Event FOR EACH ROW WHEN (NEW.timestamp < datetime().minusYears(7)) EXECUTE GQL -- Move to archive CREATE (a:ArchivedEvent { original_id: NEW.id, data: NEW.properties, archived_at: datetime() }) IN GRAPH archive_db; -- Delete from active DELETE NEW; -- Schedule periodic retention checks CREATE SCHEDULED TRIGGER retention_cleanup EVERY '1 day' EXECUTE GQL MATCH (e:Event) WHERE e.timestamp < datetime().minusYears(7) WITH e LIMIT 1000 DELETE e; </code></pre></div> <h4 id="bi-directional-relationship-sync" class="position-relative d-flex align-items-center group"> Bi-Directional Relationship Sync <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="bi-directional-relationship-sync" aria-haspopup="dialog" aria-label="Share link: Bi-Directional Relationship Sync"> Share link </button> </h4>Maintain symmetric relationships: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- When A friends B, also create B friends A CREATE TRIGGER bidirectional_friendship AFTER INSERT ON [:FRIEND] FOR EACH RELATIONSHIP WHEN NOT EXISTS { MATCH (target)-[:FRIEND]->(source) WHERE source = startNode(NEW) AND target = endNode(NEW) } EXECUTE GQL MATCH (a), (b) WHERE id(a) = id(startNode(NEW)) AND id(b) = id(endNode(NEW)) CREATE (b)-[:FRIEND { created_at: NEW.created_at, bidirectional: true }]->(a); -- When A unfriends B, also remove B friends A CREATE TRIGGER bidirectional_unfriend AFTER DELETE ON [:FRIEND] FOR EACH RELATIONSHIP EXECUTE GQL MATCH (target)-[r:FRIEND]->(source) WHERE source = startNode(OLD) AND target = endNode(OLD) DELETE r; </code></pre></div> <h4 id="denormalization-maintenance" class="position-relative d-flex align-items-center group"> Denormalization Maintenance <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="denormalization-maintenance" aria-haspopup="dialog" aria-label="Share link: Denormalization Maintenance"> Share link </button> </h4>Keep denormalized data in sync: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Update user's post count when posts created/deleted CREATE TRIGGER maintain_post_count AFTER INSERT OR DELETE ON Post FOR EACH ROW EXECUTE GQL MATCH (u:User {id: COALESCE(NEW.author_id, OLD.author_id)}) WITH u, CASE WHEN INSERTING THEN 1 WHEN DELETING THEN -1 END AS delta SET u.post_count = u.post_count + delta, u.last_post_at = CASE WHEN INSERTING THEN datetime() ELSE u.last_post_at END; -- Maintain aggregated statistics CREATE TRIGGER update_category_stats AFTER INSERT OR UPDATE OR DELETE ON Product FOR EACH ROW EXECUTE GQL MATCH (c:Category {id: COALESCE(NEW.category_id, OLD.category_id)}) WITH c, COUNT{(c)<-[:IN_CATEGORY]-(p:Product)} AS product_count, AVG{(c)<-[:IN_CATEGORY]-(p:Product) | p.price} AS avg_price SET c.product_count = product_count, c.average_price = avg_price, c.last_updated = datetime(); </code></pre></div> <h4 id="soft-delete-implementation" class="position-relative d-flex align-items-center group"> Soft Delete Implementation <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="soft-delete-implementation" aria-haspopup="dialog" aria-label="Share link: Soft Delete Implementation"> Share link </button> </h4>Mark records as deleted instead of removing them: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Intercept deletes and convert to soft delete CREATE TRIGGER soft_delete_user INSTEAD OF DELETE ON Person FOR EACH ROW EXECUTE GQL MATCH (p:Person) WHERE id(p) = id(OLD) SET p.deleted_at = datetime(), p.deleted_by = CURRENT_USER(), p.status = 'deleted' REMOVE p:Active SET p:Deleted; -- Hide soft-deleted records from normal queries CREATE VIEW ActivePersons AS MATCH (p:Person) WHERE p.deleted_at IS NULL RETURN p; </code></pre></div> <h3 id="hook-performance-optimization" class="position-relative d-flex align-items-center group"> Hook Performance Optimization <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hook-performance-optimization" aria-haspopup="dialog" aria-label="Share link: Hook Performance Optimization"> Share link </button> </h3> <h4 id="conditional-execution" class="position-relative d-flex align-items-center group"> Conditional Execution <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="conditional-execution" aria-haspopup="dialog" aria-label="Share link: Conditional Execution"> Share link </button> </h4>Minimize trigger overhead: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Only execute for significant changes CREATE TRIGGER notify_price_change AFTER UPDATE ON Product FOR EACH ROW WHEN (ABS(NEW.price - OLD.price) / OLD.price > 0.1) -- >10% change EXECUTE GQL CREATE (n:PriceChangeNotification { product_id: NEW.id, old_price: OLD.price, new_price: NEW.price, change_percent: (NEW.price - OLD.price) / OLD.price * 100, timestamp: datetime() }); </code></pre></div> <h4 id="batching-hook-actions" class="position-relative d-flex align-items-center group"> Batching Hook Actions <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="batching-hook-actions" aria-haspopup="dialog" aria-label="Share link: Batching Hook Actions"> Share link </button> </h4>Process multiple rows efficiently: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Per-statement trigger (not per-row) CREATE TRIGGER batch_audit_inserts AFTER INSERT ON User FOR EACH STATEMENT EXECUTE GQL -- NEW represents all inserted rows WITH NEW UNWIND NEW AS user CREATE (a:AuditLog { action: 'user_created', user_ids: [u IN NEW | u.id], count: SIZE(NEW), timestamp: datetime() }); </code></pre></div> <h4 id="async-hook-execution" class="position-relative d-flex align-items-center group"> Async Hook Execution <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="async-hook-execution" aria-haspopup="dialog" aria-label="Share link: Async Hook Execution"> Share link </button> </h4>Defer non-critical work: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Queue for async processing CREATE TRIGGER queue_welcome_email AFTER INSERT ON User FOR EACH ROW EXECUTE GQL CREATE (job:AsyncJob { type: 'send_welcome_email', user_id: NEW.id, user_email: NEW.email, queued_at: datetime(), status: 'pending' }); -- Background worker processes jobs -- (separate process, no blocking on insert) </code></pre></div> <h3 id="hook-testing-strategies" class="position-relative d-flex align-items-center group"> Hook Testing Strategies <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="hook-testing-strategies" aria-haspopup="dialog" aria-label="Share link: Hook Testing Strategies"> Share link </button> </h3> <h4 id="unit-testing-hooks" class="position-relative d-flex align-items-center group"> Unit Testing Hooks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="unit-testing-hooks" aria-haspopup="dialog" aria-label="Share link: Unit Testing Hooks"> Share link </button> </h4>Test trigger logic in isolation: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">import pytest from geode_client import Client @pytest.fixture async def client(): client = await Client.connect("localhost:3141") yield client await client.close() @pytest.mark.asyncio async def test_audit_trigger(client): """Test audit log trigger creates entries""" # Insert user (triggers audit hook) await client.execute(""" CREATE (:User {id: 123, name: 'Test User', email: '[email protected]'}) """) # Verify audit log created result, _ = await client.query(""" MATCH (a:AuditLog) WHERE a.action = 'user_created' AND a.record_id = 123 RETURN a """) assert len(result.bindings) == 1 audit = result.bindings[0]['a'] assert audit['action'] == 'user_created' assert '[email protected]' in audit['details'] @pytest.mark.asyncio async def test_validation_trigger(client): """Test validation trigger rejects invalid data""" with pytest.raises(Exception, match="Invalid age value"): await client.execute(""" CREATE (:Person {id: 456, name: 'Invalid', age: -5}) """) # Verify no record created result, _ = await client.query(""" MATCH (p:Person {id: 456}) RETURN p """) assert len(result.bindings) == 0 </code></pre></div> <h4 id="integration-testing" class="position-relative d-flex align-items-center group"> Integration Testing <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="integration-testing" aria-haspopup="dialog" aria-label="Share link: Integration Testing"> Share link </button> </h4>Test hooks with full workflows: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-python" data-lang="python">@pytest.mark.asyncio async def test_bidirectional_friendship(client): """Test bidirectional friendship trigger""" # Create users await client.execute(""" CREATE (:User {id: 1, name: 'Alice'}) CREATE (:User {id: 2, name: 'Bob'}) """) # Create friendship (triggers bidirectional hook) await client.execute(""" MATCH (a:User {id: 1}), (b:User {id: 2}) CREATE (a)-[:FRIEND {created_at: datetime()}]->(b) """) # Verify both directions exist result, _ = await client.query(""" MATCH (a:User {id: 1})-[:FRIEND]->(b:User {id: 2}), (b)-[:FRIEND]->(a) RETURN a.name, b.name """) assert len(result.bindings) == 1 </code></pre></div> <h3 id="debugging-hooks" class="position-relative d-flex align-items-center group"> Debugging Hooks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="debugging-hooks" aria-haspopup="dialog" aria-label="Share link: Debugging Hooks"> Share link </button> </h3> <h4 id="trigger-execution-log" class="position-relative d-flex align-items-center group"> Trigger Execution Log <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-execution-log" aria-haspopup="dialog" aria-label="Share link: Trigger Execution Log"> Share link </button> </h4>Track trigger executions: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Enable trigger logging SET trigger_logging = true; -- Execute operation CREATE (:User {id: 789, name: 'Debug User'}); -- View trigger log SELECT trigger_name, execution_time_ms, status, error_message FROM SYSTEM.trigger_execution_log WHERE timestamp > datetime().minusMinutes(5) ORDER BY timestamp DESC; </code></pre></div> <h4 id="trigger-profiling" class="position-relative d-flex align-items-center group"> Trigger Profiling <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="trigger-profiling" aria-haspopup="dialog" aria-label="Share link: Trigger Profiling"> Share link </button> </h4>Identify slow triggers: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Profile trigger execution PROFILE TRIGGER audit_new_users; -- Returns: -- Trigger: audit_new_users -- Execution time: 2.5ms -- Breakdown: -- Pattern matching: 0.3ms -- Property access: 0.1ms -- Insert operation: 2.1ms </code></pre></div> <h3 id="migration-strategies" class="position-relative d-flex align-items-center group"> Migration Strategies <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="migration-strategies" aria-haspopup="dialog" aria-label="Share link: Migration Strategies"> Share link </button> </h3> <h4 id="adding-hooks-to-existing-data" class="position-relative d-flex align-items-center group"> Adding Hooks to Existing Data <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="adding-hooks-to-existing-data" aria-haspopup="dialog" aria-label="Share link: Adding Hooks to Existing Data"> Share link </button> </h4>Apply triggers to historical data: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Create trigger CREATE TRIGGER compute_full_name BEFORE INSERT OR UPDATE ON Person FOR EACH ROW EXECUTE GQL SET NEW.full_name = NEW.first_name || ' ' || NEW.last_name; -- Backfill existing data MATCH (p:Person) WHERE p.full_name IS NULL WITH p LIMIT 1000 SET p.full_name = p.first_name || ' ' || p.last_name; -- Repeat until all records updated </code></pre></div> <h4 id="versioning-hooks" class="position-relative d-flex align-items-center group"> Versioning Hooks <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="versioning-hooks" aria-haspopup="dialog" aria-label="Share link: Versioning Hooks"> Share link </button> </h4>Manage hook changes over time: <div class="highlight"><pre tabindex="0" class="chroma"><code class="language-gql" data-lang="gql">-- Drop old trigger DROP TRIGGER IF EXISTS audit_new_users_v1; -- Create new version CREATE TRIGGER audit_new_users_v2 AFTER INSERT ON Person FOR EACH ROW EXECUTE GQL INSERT (a:AuditLog { version: 2, -- Track trigger version action: 'user_created', user_id: NEW.id, timestamp: NOW(), details: NEW.properties, ip_address: CURRENT_IP(), -- New field in v2 user_agent: CURRENT_USER_AGENT() -- New field in v2 }); </code></pre></div> <h3 id="learn-more" class="position-relative d-flex align-items-center group"> Learn More <button type="button" class="h-share btn btn-link p-0 text-decoration-none link-secondary opacity-50 hover-opacity-100 transition-all ms-1" data-share-target="learn-more" aria-haspopup="dialog" aria-label="Share link: Learn More"> Share link </button> </h3><ul> <li><a href="https://www.postgresql.org/docs/current/triggers.html" aria-label="Database Triggers (PostgreSQL) – opens in new window" target="_blank" rel="noopener noreferrer" >Database Triggers (PostgreSQL) ↗ </a> </li> <li><a href="https://use-the-index-luke.com/sql/dml/insert" aria-label="Trigger Design Patterns – opens in new window" target="_blank" rel="noopener noreferrer" >Trigger Design Patterns ↗ </a> </li> <li><a href="https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-triggers.html" aria-label="Oracle Triggers Documentation – opens in new window" target="_blank" rel="noopener noreferrer" >Oracle Triggers Documentation ↗ </a> </li> <li><a href="https://martinfowler.com/articles/201701-event-driven.html" aria-label="Event-Driven Architecture – opens in new window" target="_blank" rel="noopener noreferrer" >Event-Driven Architecture ↗ </a> </li> <li><a href="https://www.postgresql.org/docs/current/plpgsql-trigger.html" aria-label="Database Automation Best Practices – opens in new window" target="_blank" rel="noopener noreferrer" >Database Automation Best Practices ↗ </a> </li> </ul>

Popular

Related Articles