Creating a Trigger in Oracle PL/SQL

• Unauthorized updates to sensitive data.
• Exceeding a predefined threshold (e.g., unusually high transaction amounts).
• Insertion of invalid or suspicious data.

• INSERT: Triggered when a new record is added.
• UPDATE: Triggered when an existing record is modified.
• DELETE: Triggered when a record is removed.

• BEFORE: To validate or modify data before the operation.
• AFTER: To audit or notify after the operation is complete.

• Using PL/SQL to check for abnormal conditions.
• Logging anomalies to a table for further analysis.
• Sending notifications via email or other communication channels.

CREATE OR REPLACE TRIGGER notify_anomaly
AFTER INSERT OR UPDATE ON transactions
FOR EACH ROW
BEGIN
    IF :NEW.transaction_amount > 100000 THEN
        -- Log anomaly
        INSERT INTO anomaly_log (transaction_id, message, logged_at)
        VALUES (:NEW.transaction_id, 'Transaction exceeds threshold', SYSDATE);

        -- Send email notification
        UTL_MAIL.SEND(
            sender => 'alerts@yourcompany.com',
            recipients => 'admin@yourcompany.com',
            subject => 'Anomaly Detected',
            message => 'Transaction ' || :NEW.transaction_id || 
                       ' exceeds the threshold amount.'
        );
    END IF;
END;
/
            

• Use the DBMS_ALERT package to notify users of specific events.
• Use the DBMS_PIPE package to communicate between database sessions.

CREATE OR REPLACE TRIGGER alert_anomaly
AFTER INSERT ON transactions
FOR EACH ROW
BEGIN
    IF :NEW.transaction_amount > 100000 THEN
        DBMS_ALERT.SIGNAL('HIGH_TRANSACTION', 'Anomaly detected: High transaction amount.');
    END IF;
END;
/