Oracle interview Question : what is trigger in oracle

Summary

This video series educates SQL PL/SQL developers on Oracle interview questions, focusing on triggers. It explains triggers as event-based programming in databases, using an ATM analogy to illustrate their automatic response to database events like insert, update, or delete. The video covers various types of triggers, including DML, DDL, system, instead of, and compound triggers, and their purposes such as auditing, enforcing complex integrity, logging, security enforcement, data replication, and preventing invalid transactions. It also touches on the use of system triggers for auditing system events and startup/shutdown processes.

Takeaways

• 📌 Triggers are PL/SQL code blocks that automatically execute in response to a database event.
• 🔑 The analogy of an ATM sending SMS or email notifications upon a transaction is used to explain the concept of triggers.
• 🚀 Triggers can be classified based on the type of event they respond to: DML, DDL, or system events.
• 🔄 DML triggers are activated by data manipulation language events like INSERT, UPDATE, or DELETE.
• 🛠 DDL triggers respond to data definition language events such as CREATE, ALTER, or DROP.
• 🌐 System triggers are invoked by system events like logon, logoff, or database startup and shutdown.
• 👉 Instead of triggers are used with views to redirect DML operations to underlying base tables.
• 🔩 Compound triggers allow for multiple DML operations to be handled within a single trigger.
• 🔒 Triggers serve various purposes, including auditing, enforcing complex referential integrity, logging, enforcing security, data replication, and preventing invalid data or transactions.
• 🔄 Triggers can be used to audit DDL transactions, ensuring that changes to database structures are tracked.
• 🛡 System triggers are useful for monitoring and controlling user sessions and database activity, such as login times and session durations.
• 🔄 Startup and shutdown triggers can be used for cleanup operations when the database starts or shuts down.

Q & A

What is a trigger in the context of SQL PL/SQL?

A trigger in SQL PL/SQL is a PL/SQL code block that is automatically executed in response to certain events on a database, such as DML operations (INSERT, UPDATE, DELETE), DDL events, or system events.

Can you provide an analogy to explain how triggers work?

Yes, an analogy for triggers is an ATM transaction. When a person withdraws or deposits money, they receive an SMS and an email automatically, without any manual action required. Similarly, triggers automatically execute code in response to database events.

What are the different types of triggers mentioned in the script?

The script mentions DML triggers, DDL triggers, system triggers, instead of triggers, and compound triggers. DML triggers respond to data manipulation events, DDL triggers respond to data definition events, system triggers respond to system events, instead of triggers are used with views, and compound triggers can handle multiple DML events in one trigger.

How are DML triggers different from instead of triggers?

DML triggers are written on top of a table and are automatically invoked when an INSERT, UPDATE, or DELETE operation occurs on the table. Instead of triggers, on the other hand, are written on top of a view and can redirect the DML operations to the underlying base tables.

What is the purpose of using a compound trigger?

A compound trigger allows you to handle multiple DML events within a single trigger. Instead of writing separate triggers for each DML operation, you can consolidate the logic into one compound trigger for efficiency.

What are some common purposes of using triggers in a database?

Common purposes of triggers include auditing changes, enforcing complex referential integrity, logging data changes, enforcing security, data replication across multiple tables, preventing invalid data or transactions, and auditing DDL transactions.

How can triggers help in enforcing security within a database?

Triggers can be used to enforce security by blocking certain transactions. For example, if a DELETE operation is not allowed on a specific table, a trigger can be set to throw an exception and prevent the deletion from occurring.

What is the role of system triggers in database auditing?

System triggers are used to audit system-related events such as user logins, logouts, database startups, and shutdowns. They can capture who performed an action, when it was performed, and how long a session was active.

Can triggers be used to replicate data across multiple tables?

Yes, triggers can be used for data replication. For instance, when a new record is inserted into one table, a trigger can automatically copy the data to other related tables to maintain consistency across the database.

How can triggers be used to prevent invalid data entries?

Triggers can include conditional checks to validate data before it is committed to the database. If a data entry does not meet the specified criteria, the trigger can block the transaction and potentially raise an error or exception.

What is the significance of DDL triggers in database management?

DDL triggers are significant for auditing and controlling DDL statements like CREATE, ALTER, DROP, GRANT, and REVOKE. They can also be used to prevent unauthorized modifications to the database structure, such as blocking the DROP statement to protect objects from being accidentally deleted.