How many types of database triggers can be specified on a table? What are they?

Editorial / Best Answer

krishnaindia2007  

  • Member Since Sep-2007 | May 6th, 2008


A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

Showing Answers 1 - 11 of 11 Answers

Paramjeet singh

  • Sep 15th, 2005
 

Before and After

  Was this answer useful?  Yes

pravinkumar

  • Dec 30th, 2005
 

There are five types of database triggers

  1. Row Level Trigger
  2. Statement Level Trigger
  3. Instead of Trigger
  4. Schema Trigger
  5. Database Trigger

Out of these five types of triggers Only two are used for the table purpose, i.e. two row level trigger and statement level triggers are  used for insert, update or/and delete operation on table

KIRAN

  • Feb 8th, 2006
 

the different types of triggers: * Row Triggers and Statement Triggers * BEFORE and AFTER Triggers * INSTEAD OF Triggers * Triggers on System Events and User Events

  Was this answer useful?  Yes

SQLTweety

  • May 16th, 2007
 

Pravin,
 those are ORACLE triggers.
For SQL only three Insert , update , delete.
And they are POST and INSTEAD of triggers for SQL server.

  Was this answer useful?  Yes

g_sidhu

  • Feb 16th, 2008
 

A single DML statement can potentially fire up to four types of triggers: BEFORE and AFTER statement and row triggers. 1 instead of triggers to manipulate complex views from 9i onwards new triggers have been introduced to logon and log off.

A trigger may be a
1. DML Trigger on tables
2. Instead of triggers on views
3. System triggers on database or schema

Based on the way it executes statements  triggers are of two types
1. Statement leve trigger
2.  Row level trigger

A trigger fires for three actions
1. Insert
2. Delete 
3.Update

and the trigger can the fired
1. Before action
2. After action.

There are a total of 12 possible types:

3 Statements (INSERT, UPDATE OR DELETE)
2
Timing (BEFORE or AFTER)
2 Levels (ROW or STATEMENT)

So 3*2*2=12 possible types.
A table can have 12 triggers defined on it.

ORA_CRAZY

  • Nov 19th, 2009
 

Triggers can be classified based on the below factors -

(1) Trigger Event:
INSERT
UPDATE
DELETE

(2) Trigger Type:
STATEMENT Level
ROW Level

(3) Trigger Timings:
BEFORE
AFTER
INSTEAD OF

Below mentioned triggers can be specified on Table

1.ROW or STATEMENT

2.BEFORE or AFTER or INSTEAD OF Triggers

3.DML (INSERT, UPDATE, or DELETE on table or view ) or DDL (CREATE, ALTER, or DROP on schema objects which includes all DB objetcs)

4.COMPOUND TRIGGERS

  Was this answer useful?  Yes

tarini sankar das

  • Feb 7th, 2014
 

Actual answer is we have 14 types of triggers in pl sql.
For statement level :-
1-before insert
2-before update
3-before delete
4-after insert
5-after update
6-after delete
For row level :-
7-before insert
8-before update
9-before delete
10-after insert
11-after update
12-after delete
And the rest two are
13-instead of trigger
14-system trigger

  Was this answer useful?  Yes

Pradeep

  • Feb 20th, 2016
 

Basically 5 main types in Oracle Triggers
1. DML Trigger (3 statement * 2 timing * 2 level = 12 types).
2. DDL Trigger.
3. Instead of Trigger (Fired Complex View).
4. System Trigger.
5. Compound Trigger (Introduced by 11g)

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions