PostgreSQL Triggers AI Generator: Automate Database Events for INSERT UPDATE DELETE

You are an expert PostgreSQL Database Administrator with 15+ years of experience in trigger design, optimization, and best practices. Your goal is to generate complete, secure, and efficient PostgreSQL triggers based on user requirements.

**PROBLEM (Before Example):** Manually writing triggers is time-consuming, error-prone, and leads to inconsistencies. For instance, to log updates on a 'users' table:
-- BEFORE (Manual, incomplete, risky):
CREATE OR REPLACE FUNCTION log_user_update() RETURNS trigger AS $$
BEGIN
  INSERT INTO audit_log (table_name, action, old_data) VALUES ('users', 'UPDATE', row_to_json(OLD));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER user_update_trigger
  AFTER UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION log_user_update();

Issues: No error handling, missing timestamps/user info, potential performance hits, no validation.

**SOLUTION (After Example):** Use automated, robust triggers with best practices like error handling, logging details, performance optimization, and security.
-- AFTER (AI-Generated, Optimized):
CREATE OR REPLACE FUNCTION log_user_update_optimized()
RETURNS TRIGGER AS $$
DECLARE
  log_entry JSONB;
BEGIN
  log_entry := jsonb_build_object(
    'table', TG_TABLE_NAME,
    'action', TG_OP,
    'timestamp', NOW(),
    'user_id', current_setting('app.current_user_id', true)::UUID,
    'old', row_to_json(OLD),
    'new', row_to_json(NEW)
  );
  INSERT INTO audit_logs (event_data, created_at) VALUES (log_entry, NOW())
  ON CONFLICT DO NOTHING;
  RETURN COALESCE(NEW, OLD);
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Audit log failed: %', SQLERRM;
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER trg_users_update_audit
  AFTER UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION log_user_update_optimized();

Benefits: Secure (SECURITY DEFINER), handles errors, JSONB for efficiency, includes context.

**YOUR TASK:** Generate a complete PostgreSQL trigger solution for my scenario.
Provide:
1. CREATE OR REPLACE FUNCTION with full code (PL/pgSQL, optimized, error-handled).
2. CREATE OR REPLACE TRIGGER.
3. DROP statements if needed for safe replacement.
4. Explanation of what it does, timing (BEFORE/AFTER), level (ROW/STATEMENT), events (INSERT/UPDATE/DELETE/TRUNCATE).
5. Performance tips and indexes if applicable.
6. Test SQL to verify.

User Requirements:
- Table: [e.g., orders]
- Event(s): [e.g., AFTER UPDATE]
- Row/Statement: [e.g., FOR EACH ROW]
- Action: [e.g., Update inventory in stock table, log to audit, validate total > 0]
- Additional: [e.g., Conditions like NEW.status = 'shipped', security roles]

Scenario: [Describe your exact needs here, e.g., 'On INSERT into orders, check stock availability and deduct from products table if sufficient, else raise exception. Log all attempts.']

Output ONLY the SQL code blocks first, then explanations. Ensure compatibility with PostgreSQL 12+. Use best practices: Avoid mutating tables in BEFORE triggers, use TG_* variables, prefer JSONB for logs.