Term
|
Definition
|
|
Term
| Name the 2 modes of DML triggers. |
|
Definition
|
|
Term
| When does an DML trigger in AFTER mode fire? |
|
Definition
| Fires after SQL Server completes all actions successfully. If any FKs, contraints, or other fails, the trigger never fires. p.367 |
|
|
Term
| When does an DML trigger in INSTEAD OF mode fire? |
|
Definition
| it fires and executes INSTEAD OF the operation that caused it to fire. So the INSERT/UPDATE/DELETE never takes place, no validations are performed, just the trigger code. p.367 |
|
|
Term
| How many INSTEAD OF triggers can be created per table? |
|
Definition
| 3. One per INSERT/UPDATE/DELETE p.368 |
|
|
Term
| What type of tables/views do not support triggers? |
|
Definition
| system tables and DMVs p.368 |
|
|
Term
| What type of operations will bypass triggers? |
|
Definition
| Minimally logged operations such as TRUNCATE and WRITETEXT do not cause a trigger to fire. p. 368 |
|
|
Term
| What is the WRITETEXT command used for? |
|
Definition
| to update text, ntext, or image data types. |
|
|
Term
| Why are FKs preferred over triggers to enforce referential integrity. |
|
Definition
| Because an FK will refuse the operation BEFORE the data has been written to the table. p.369 |
|
|
Term
| Why are triggers necessary to enforce referential integrity. |
|
Definition
| required to enforce referential integrity across databases. an FK cannot be defined across databases. p.368 |
|
|
Term
| At what level can a DDL trigger be created? |
|
Definition
| ALL SERVER | DATABASE p.371 |
|
|
Term
|
Definition
| A stored procedure that fires in response to a LOGON event. |
|
|
Term
| When does a logon trigger fire? |
|
Definition
| Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. All messages are diverted to the Error Log. Logon triggers do not fire if authentication fails. |
|
|
Term
| LOGON event VS. AUDIT_LOGIN SQL Trace event |
|
Definition
LOGON Trigger events are raised synchronously.
AUDIT_LOGIN SQL Trace event are asynchronous.
This means, for example, that if you want to stop a session from being established, you must use a logon trigger. An event notification on an AUDIT_LOGIN event cannot be used for this purpose. |
|
|
