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
EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Effects of NULL in Aggregate Operator - SQL Let aggop(x) be an invocation of some aggregate operator aggop in SQL, where x is an expression (usually an open expression) to be

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your

1- You can check attribute names from each table in DBF11 by running for example:  desc dbf11.Member;  desc dbf11.Agent;  desc dbf11.Producer; Because some attribute names in

Benefit of the dynamic SQL: This part shows you how to take full benefit of the dynamic SQL and how to keep away from some of the common pitfalls. Passing the Names of Sc

What Is a Package? The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a bo

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.