ORACLE SQL 1.)Let\'s say you had a column in the Student table named \'Gender\'.

Question

ORACLE SQL

1.)Let's say you had a column in the Student table named 'Gender'. Datatype is char(1). Values for all rows for Gender are 'X'.

You want to add a new check constraint to restrict the values to be 'M' or 'F'. If you tried to add that check constraint, it would fail because you have data that already violated the constraint (all those 'X' values).

Here's the SQL to add the check constraint:

ALTER TABLE STUDENT
ADD CONSTRAINT STUDENT_CHK1 CHECK
(gender in ('M','F') )
ENABLE ;

What option could you add on this constraint that would ignore the existing values? In other words... the 'X' values are ignored, but any new insert/update will check the constraint...?

2.)For triggers... if you had an update trigger... and you wanted to abort the update (stop the update) what is the strategy?

What is the predicate for the trigger?
What action can you do to stop the update?

3.)For triggers... if you wanted capture information every time someone created or dropped an object... what is the trigger signature.

4.)How do you write a trigger that would stop an update any time COST in COURSE is increased by more than 10%.

I only want this trigger to fire if COST is updated.

5.)For triggers... give an example of using the 'when' clause in the trigger signature. When should 'when' be used?

Explanation / Answer

Solutions:

1. ALTER TABLE STUDENT
ADD CONSTRAINT STUDENT_CHK1 CHECK
(gender in ('M','F') )
ENABLE NOVALIDATE;

(When you enable NOVALIDATE, existing data does not have to conform to the constraint).

---------------------------------------------------------------------------------------------------------------------------

2. Create triggers with the CREATE TRIGGER statement. They can be defined as firing BEFORE or AFTER the triggering event, or INSTEAD OF it.

Ex:

ou can later remove a trigger from the database by issuing the DROP TRIGGER statement.

A trigger can be in either of two distinct modes:

Enabled

An enabled trigger executes its trigger body if a triggering statement is issued and the trigger restriction, if any, evaluates to true. By default, triggers are enabled when first created.

Disabled

A disabled trigger does not execute its trigger body, even if a triggering statement is issued and the trigger restriction (if any) evaluates to true.

To enable or disable triggers using the ALTER TABLE statement, you must own the table, have the ALTER object privilege for the table, or have the ALTER ANY TABLE system privilege. To enable or disable an individual trigger using the ALTER TRIGGER statement, you must own the trigger or have the ALTER ANY TRIGGER system privilege.

Enabling Triggers

You enable a disabled trigger using the ALTER TRIGGER statement with the ENABLE option. To enable the disabled trigger named reorder on the inventory table, enter the following statement:

To enable all triggers defined for a specific table, use the ALTER TABLE statement with the ENABLE ALL TRIGGERS option. To enable all triggers defined for the INVENTORY table, enter the following statement:

-----------------------------------------------------

Disabling Triggers

Consider temporarily disabling a trigger if one of the following conditions is true:

An object that the trigger references is not available.

You must perform a large data load and want it to proceed quickly without firing triggers.

You are loading data into the table to which the trigger applies.

You disable a trigger using the ALTER TRIGGER statement with the DISABLE option. To disable the trigger reorder on the inventory table, enter the following statement:

You can disable all triggers associated with a table at the same time using the ALTER TABLE statement with the DISABLE ALL TRIGGERS option. For example, to disable all triggers defined for the inventory table, enter the following statement:

---------------------------------------------------------------------------------------------------------------------------

3. -- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] [,] [DROP]} [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > } <dml_trigger_option> ::= [ ENCRYPTION ] [ EXECUTE AS Clause ] <method_specifier> ::= assembly_name.class_name.method_name

---------------------------------------------------------------------------------------------------------------------------------

4.

Create or Replace Trigger SomeTriggerName
Before Update On COST
For Each Row
Begin
IF (:new.cost > (:old.cost * 1.1)) Then
RAISE_APPLICATION_ERROR(-20004, 'Cost in course is increased more than 10%, which is not allowed.');
End IF;
End;

-----------------------------------------------------------------------------------------------------------------------------------------------

5. The WHEN clause is a Boolean expression that is evaluated by Oracle before the trigger’s body is executed. Using the WHEN clause allows a developer to improve performance by testing simple conditions before the trigger’s body is executed.

Example:

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.