Procedural constraint enforcement (triggers) , PL-SQL Programming

Procedural Constraint Enforcement (Triggers)

SQL has an alternative method of addressing database integrity, involving event-driven procedural code. The special procedures that can be used for this purpose are called triggers and the events that activate them are specified update operations. For example, suppose it is required for every row in IS_CALLED to have a matching row on StudentId in IS_ENROLLED_ON, enforcing a business rule to the effect that every registered student must be enrolled on at least one course. Then a triggered procedure might be activated every time INSERT is used to add a row to IS_CALLED, checking to see if a matching row exists in IS_ENROLLED_ON and raising an exception if there isn't one. But that wouldn't be sufficient to address the requirement.

Further triggers would be needed, activated by UPDATE statements on IS_CALLED and IS_ENROLLED_ON that cause changes to StudentId values in either of those tables, and by DELETE statements on IS_ENROLLED_ON. As this simple example demonstrates, use of triggered procedures for constraint enforcement can be complicated and error-prone. As one practitioner told me, "It quickly gets so complicated that it's almost impossible for a human not to make errors..., and even when you're not facing a 'complicated' case, the work to be done is tedious and boring".

Posted Date: 1/18/2013 7:25:02 AM | Location : United States







Related Discussions:- Procedural constraint enforcement (triggers) , Assignment Help, Ask Question on Procedural constraint enforcement (triggers) , Get Answer, Expert's Help, Procedural constraint enforcement (triggers) Discussions

Write discussion on Procedural constraint enforcement (triggers)
Your posts are moderated
Related Questions
UPDATE Command- SQL Loosely speaking, UPDATE changes some of the column values of some existing rows of its target table. Thus, although some rows disappear from the target an

Defining Autonomous Transactions To define an autonomous transaction, you use the pragma (compiler directive) AUTONOMOUS_TRANSACTION. The pragma instructs the PL/SQL compiler

What are 3 good practices of modeling and/or implementing data warehouses?

Restriction in SQL Syntax: SELECT DISTINCT StudentId FROM IS_CALLED WHERE Name = 'Boris' The WHERE clause operates on the result of the FROM clause in analogo

Seeking a programmer to design a legal document with pre-existing fields that could allow the auto-population of client(s) information (i.e. Name, Account Number, Address etc.) int

Table Comparison - SQL The following definitions for relation comparisons: Let r1 and r2 be relations having the same heading. Then: r1 ⊆ r2 is true if every tuple of r1

Using EXCEPTION_INIT To handle unnamed internal exceptions, you should use the OTHERS handler or the pragma EXCEPTION_INIT. The pragma is a compiler directive that can be th

GOTO Statement The GOTO statement branches to a label unconditionally. The label must be exclusive within its scope and should precede an executable statement or a PL/SQL block.

Updating by insertion Syntax : INSERT INTO ENROLMENT VALUES (SID ('S4'), 'Devinder', CID ('C1'));

Using the FORALL Statement The keyword FORALL instruct the PL/SQL engine to bulk-bind input collections before sending them all to the SQL engine. Though the FORALL statement