DML statements are fully logged if there is an active trigger for that DML operation on the table. If a trigger is created and logging is disabled on the table, a warning is raised, indicating that the DML statements will operate in fully logged mode. However, the trigger is successfully created.
The logging mode of DML statements in triggers varies, depending on the user who fired the trigger. Tables owned by the same user who fired the trigger can be operated on in minimally logged mode. DML statements executed on tables that are not owned by the user who fired the trigger are executed in fully logged mode, unless these tables are explicitly set up for minimal logging.
Consider delete_trig_m1, a delete trigger on the object m1 owned by user Mary. This trigger performs DML statements on other tables, such as Joe.j2 and Paul.p3, in minimally logged mode. delete privilege has been granted on Mary.m1 to user Sally.
Create trigger Mary.delete_trig_m1 FOR DELETE On Mary.m1 as Begin Delete Mary.min_logged_table_t3 WHERE ... SET DML_LOGGING MINIMAL DELETE Paul.p3 WHERE ... End
When user Sally executes the delete statement
on Mary.m1, the trigger, Mary.delete_trig_m1 is
fired and privilege checks are performed on the appropriate tables
on behalf of Sally. Because Mary owns the trigger, Adaptive Server
peforms no permission checks for the delete Mary.min_logged_table_t3 statement,
where the DML statement is run in minimally logged mode. (The table
has been defined for minimal logging through some other means.) Because
Sally does not have the privilege to turn off dml_logging for Paul.p3,
the set command does not take effect silently. The next
statement, delete Paul.p3 where...,
runs in fully logged mode. When user Mary executes the trigger,
the DML logging behavior is the same (fully logged).
However, if user Paul executes the outer delete on Mary.m1, causing the trigger to fire, then both the statements in the body of the trigger execute in minimally logged mode.
Once the trigger fires, the outer DML statement must have been fully logged; attempts to perform minimally logged DML on the same outer table in the body of the trigger are ignored, and these DML statements are fully logged.
Logging mode cannot be disabled on views, so instead of triggers, which are currenly only supported on views, are unaffected by the logging mode of the base tables referenced by the view, or by the session-level setting of the logging mode when the DML statements on the view with the instead of trigger executes. However, the rules for chosing logged or minimally logged modes on multiple tables, using of the set command inside the trigger body, and transactional semantics all apply for the DML statements inside the instead of trigger.
