Trigger

what are the types of triggers present in oracle and in sql server, I know in oracle there are 7 and in sql server there are 13 or vice versa. Can anyone state all of them and define each...




Moiz
LVL 4
Moizsaif123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DrSQLCommented:
Moiz,
   I can only answer for Oracle.  There is BEFORE, AFTER, and INSTEAD OF for insert, update, and delete, which is already 9.  And they can be row level or set level (command-level), so that's 18.

A BEFORE trigger executes before the table is inserted, updated, or deleted from.  If it is a row-level trigger then you can perform operations prior to the operation being peformed.  Thus, you can change the values of the columns in the rows.

An AFTER trigger executes after tehe table has been updated, inserted, or deleted into, but before it is committed.  Again, row-lvel lets you perform operations on the rows , but in an AFTER trigger you cannot change the values.

An instead of performs its operations and the database DOES NOT insert, update, or delete.  You can use this to make views updateable, or to remove the capability to delete (perhaps to set and active flag, instead).

Good luck!
DrSQL
kamal_therockyCommented:
Hi

In Oracle the various Tirggers are

Statement Level Trigger
Row Level Trigger
After Insert Trigger
After Delete Trigger
After Update Trigger
Before Insert Trigger
Before Delete Trigger
Before Update Trigger
and also
System Level Trigger
DDL Trigger

SQL Server 2000 has many types of triggers:

After Trigger
Multiple After Triggers
Instead Of Triggers
Mixing Triggers Type

In these again After and Before Trigger comes for Insert, Delete, Update

Regards
kamal

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Moizsaif123Author Commented:
and what are system level and instead of trigger? didnt get the definition explained above, plz provide me with a sql query to explain better,:)



Moiz
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

kamal_therockyCommented:
hi

Just as an Oracle trigger fires on a specific DML event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers:

Database startup triggers - Database startup triggers can be used to invoke the dbms_shared_pool.keep procedure to pin all frequently referenced PL/SQL packages into RAM.

logon triggers - The logon triggers can be used to store login information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.

logoff triggers - Oracle logoff triggers can automatically capture elapsed session duration times for all users who have accessed your Oracle database.

Servererror triggers - With the servererror trigger, the Oracle administrator can automatically log all serious PL/SQL errors into an Oracle STATSPACK extension table. This table, in turn, has an insert trigger that e-mails the server error directly to the Oracle professional for immediate notification.

DDL triggers - Using the DDL trigger, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

Regards
kamal
kamal_therockyCommented:
Hi,

The Code of INSTEAD OF triggers is the one which Oracle server will execute when a program performs a DML operation on the complex view. Unlike a conventional BEFORE or AFTER trigger, an INSTEAD OF trigger takes the place of, rather than supplements.

Thanks
kamal
Moizsaif123Author Commented:
hmm system level triggers can really do that?

>>logon triggers - The logon triggers can be used to store login information directly inside Oracle tables, thereby providing a complete audit of all those times when users entered your system.


i thought when a person logs in, he selects the username and password, and thats it, a trigger only occurs on insert or update or delete rite? then on selecting query how the trigger occurs?



Moiz
jwittenmCommented:
system triggers fire for system level events, as kamal said.  logon, startup, etc.  create a (free) account at technet.oracle.com and you will have access to all the documentation.
Mark GeerlingsDatabase AdministratorCommented:
Yes, Oracle supports "event triggers" in addition to the SQL statement and row triggers.  Oracle statement and row triggers are based on three types of SQL statements: insert, updates and deletes.  The triggers may be either "before" or "after" each of these, so that would be six, but they can also be either "statement-level" or "row-level", so that makes 12 types of triggers.  Statement-level triggers fire only once per SQL statement, regardless of how many rows were processed, and they cannot reference values in the row(s) being processed.  Row-level triggers fire once for each row that is affected by the SQL statement, and these may adjust and/or reference actual values in the affected rows.

In addition to these 12 triggers that can fire for tables, Oracle supports "instead of" triggers that are valid only on views.  There are three of them: instead of insert, instead of update, and instead of delete.  These are similar to row-level triggers on tables, and can do whatever action is coded into them when someone issues an insert, update or delete statement on view (within the limits of what normal insert, update or delete statements can do).

Event triggers are fired by other SQL activities, like: logon, logoff, create table, alter table, database startup, database shutdown, etc. but NOT select statements (queries).  There are no Oracle triggers that fire based on queries.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.