A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

* make sure that a column is filled in with default information
* make sure that an audit row is inserted into another table
* after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Answered by Raksha , an ibibo Master, at 9:32 AM on June 18, 2008

Constraints are declaractions of conditions about the database that must remain true. These include attributed-based, tuple-based, key, and referential integrity constraints. The system checks for the violation of the constraints on actions that may cause a violation, and aborts the action accordingly. Information on SQL constraints can be found in the textbook. The Oracle implementation of constraints differs from the SQL standard, as documented in Oracle 9i SQL versus Standard SQL.

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Answered by Arti singh , an ibibo Specialist, at 9:11 AM on June 18, 2008

Constraints are declaractions of conditions about the database that must remain true. These include attributed-based, tuple-based, key, and referential integrity constraints. The system checks for the violation of the constraints on actions that may cause a violation, and aborts the action accordingly. Information on SQL constraints can be found in the textbook. The Oracle implementation of constraints differs from the SQL standard, as documented in Oracle 9i SQL versus Standard SQL.

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Answered by Sharad Singh , an ibibo Master, at 3:15 PM on May 27, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

* make sure that a column is filled in with default information
* make sure that an audit row is inserted into another table
* after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Answered by Anju Singh , an ibibo Master, at 5:22 PM on May 24, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

* make sure that a column is filled in with default information
* make sure that an audit row is inserted into another table
* after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Consider the general_comments table:


create table general_comments (
comment_id integer primary key,
on_what_id integer not null,
on_which_table varchar(50),
user_id not null references users,
comment_date date not null,
ip_address varchar(50) not null,
modified_date date not null,
content clob,
-- is the content in HTML or plain text (the default)
html_p char(1) default 'f' check(html_p in ('t','f')),
approved_p char(1) default 't' check(approved_p in ('t','f'))
);

Users and administrators are both able to edit comments. We want to make sure that we know when a comment was last modified so that we can offer the administrator a "recently modified comments page". Rather than painstakingly go through all of our Web scripts that insert or update comments, we can specify an invariant in Oracle that "after every time someone touches the general_comments table, make sure that the modified_date column is set equal to the current date-time." Here's the trigger definition:


create trigger general_comments_modified
before insert or update on general_comments
for each row
begin
:new.modified_date := sysdate;
end;
/
show errors

We're using the PL/SQL programming language, discussed in the procedural language chapter. In this case, it is a simple begin-end block that sets the :new value of modified_date to the result of calling the sysdate function.

When using SQL*Plus, you have to provide a / character to get the program to evaluate a trigger or PL/SQL function definition. You then have to say "show errors" if you want SQL*Plus to print out what went wrong. Unless you expect to write perfect code all the time, it can be convenient to leave these SQL*Plus incantations in your .sql files.

Answered by manu pal , an ibibo Citizen, at 7:20 AM on May 24, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

* make sure that a column is filled in with default information
* make sure that an audit row is inserted into another table
* after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Answered by Nagendra , an ibibo Master, at 7:17 PM on May 23, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

1). make sure that a column is filled in with default information
2). make sure that an audit row is inserted into another table
3). after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Trigger Syntax :

syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):

CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW [WHEN (<trigger_condition>)]]

Answered by Shobhit , an ibibo Master, at 6:46 PM on May 23, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to
make sure that a column is filled in with default information
make sure that an audit row is inserted into another table
after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back.

Answered by ayisha , an ibibo Master, at 5:21 PM on May 23, 2008

hi blogger A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

1). make sure that a column is filled in with default information
2). make sure that an audit row is inserted into another table
3). after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Trigger Syntax :

syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):

CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW [WHEN (<trigger_condition>)]]

Answered by laks , an ibibo Master, at 3:03 PM on May 23, 2008

A trigger is a fragment of code that you tell Oracle to run before or after a table is modified. A trigger has the power to

1). make sure that a column is filled in with default information
2). make sure that an audit row is inserted into another table
3). after finding that the new information is inconsistent with other stuff in the database, raise an error that will cause the entire transaction to be rolled back

Trigger Syntax :

syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):

CREATE [OR REPLACE] TRIGGER <trigger_name>
{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>
[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]
[FOR EACH ROW [WHEN (<trigger_condition>)]]

Answered by Ashish Yadav , an ibibo Wizard, at 2:48 PM on May 23, 2008