Database system event triggers

• US 6,405,212 B1
• Filed: 09/27/1999
• Issued: 06/11/2002
• Est. Priority Date: 09/27/1999
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Wang, Wei, Jain, Namit, Samu, Sriram
• Primary Examiner(s): Mizrahi, Diane D.
• Application Number:
  US09/405,013
• Time in Patent Office: 988 Days
• Field of Search: 707/2, 707/9, 707/102, 707/10, 707/103.R, 705/39, 709/207, 709/225
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/24565 Triggers; Constraints
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99939 Privileged access
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A technique for database management includes receiving an indication of a selected scope of a trigger. The scope is selected from a plurality of scopes of database management. The scope can be on the level of an individual table, or a schema associated with a user, or the database itself, or a system of databases, among others. The technique also includes receiving an indication of a selected event that belongs to the set of one or more events associated with the selected scope. Trigger metadata is stored that identifies both the selected scope and the selected event. A technique is also described for executing a process in this database management system. Flags indicative of one or more applicable events of the selectable events are loaded into a private cache of the process and checked when a new event occurs.

Claims

1. A method for creating triggers, the method comprising:

receiving data that indicates a selected scope for a trigger, wherein the selected scope for the trigger is selected from a plurality of scopes of database management, wherein a first scope of the plurality of scopes consists of a single database object of a particular type and a second scope of the plurality of scopes includes a plurality of database objects of the particular type, and wherein each scope of said plurality of scopes is associated with a set of one or more events;

receiving data that indicates a selected event that belongs to the set of one or more events associated with the selected scope; and

storing trigger metadata for said trigger, said trigger metadata including data identifying the selected scope and the selected event.

2. The method of claim 1, wherein the selected event is an event that is not associated with a data manipulation language operation.

3. The method of claim 1, wherein:

the plurality of scopes includes a table scope and one or more other scopes; and

the selected scope is one of the one or more other scopes.

4. The method of claim 1, further comprising storing metadata about the selected scope, wherein the metadata about the selected scope includes flags that indicate which events in the set of one or more events associated with the selected scope are associated with at least one trigger.

5. The method of claim 4, further comprising:

starting a process within the selected scope;

loading the flags into a private cache of the process; and

checking the flags when a new event of the set of one or more events associated with the selected scope occurs in the process.

6. The method of claim 5, further comprising:

in response to checking the flags, firing the trigger if the new event is the selected event.

7. The method of claim 6, wherein the step of firing the trigger comprises the steps of:

determining whether applicable trigger condition is in a shared cache; and

if the applicable trigger condition is in the shared cache, then evaluating the applicable trigger condition; and

if the applicable trigger condition is not in the shared cache, then loading the applicable trigger condition from the metadata about the selected scope into the shared cache, and evaluating the applicable trigger condition.

8. The method of claim 2 wherein the selected event is a member of a group consisting of database startup, database shutdown, database server error, user logon, user logoff, create database object, alter database object and drop database object.

9. The method of claim 2 wherein the selected event is invocation of a stored procedure.

10. The method of claim 2 wherein the selected event is a call to a database server.

11. The method of claim 2 wherein the at least one non-DML event is a call to an enterprise manager.

12. The method of claim 1 wherein the trigger data is stored in a database managed by a database server and the selected event is a change in database statistics maintained by said database server.

13. A method for executing triggers in response to events caused by a process in a database management system, the method comprising:

maintaining metadata about a particular scope, wherein said particular scope determines which one or more database objects may be associated with a single trigger, and wherein said particular scope is associated with a set of one or more events, said metadata including one or more triggers associated with the particular scope;

when the process causes a new event in the set of one or more events associated with said particular scope to occur, checking flags that indicate which events in the set of events associated with said particular scope are associated with at least one trigger; and

if the flags indicate that the new event is associated with a trigger, then firing the trigger.

14. The method of claim 13, wherein:

the particular scope is one of a plurality of scopes of database management, wherein each scope of said plurality of scopes is associated with a set of one or more events; and

the method includes the step of maintaining metadata for each scope of said plurality of scopes.

15. The method of claim 13, further comprising the step of loading into a private cache of the process the flags that indicate which events in the set of events associated with said particular scope are associated with at least one trigger.

16. The method of claim 13, wherein the step of firing further comprises:

determining whether a condition of the trigger is in a shared cache;

if the condition is in the shared cache, then evaluating the condition; and

if the condition of the trigger is not in the shared cache, then loading the condition from the metadata about the particular scope into the shared cache, and evaluating the condition.

17. The method of claim 13, wherein:

the new event has one or more event attributes;

the step of firing the trigger includes

providing a corresponding value for each of the one or more event attributes, and

evaluating a condition of the trigger in response to at least one corresponding value.

18. The method of claim 17, wherein:

the new event is an error event; and

an error attribute is a type of error.

19. The method of claim 13, wherein:

the particular scope is a schema-actor scope associated with events performed by any process initiated by a certain user.

20. The method of claim 13, wherein:

the particular scope is a schema-owner scope associated with events performed on any database object owned by a certain user.

21. The method of claim 14, wherein:

a schema-actor scope of the plurality of scopes is associated with events performed by any process initiated by a certain user; and

a schema-owner scope of the plurality of scopes is associated with events performed on any database object owned by the certain user associated with the schema.

22. The method of claim 14, wherein:

a statistics scope of the plurality of scopes is associated with events performed relating to the statistical information maintained by a database.

23. The method of claim 22, wherein:

the particular scope is the statistics scope;

the flags associated with the statistics scope indicate that an update event is associated with a threshold trigger; and

the step of firing the trigger includes

evaluating a threshold trigger condition which is satisfied if a value of a certain statistic exceeds a set threshold, and

if the threshold trigger condition is satisfied, then updating a one row table.

24. The method of claim 23, wherein:

a one-row table scope of the plurality of scopes is associated with events performed relating to the one-row table;

the flags associated with the one-row table scope indicate that an update event is associated with a cascaded trigger; and

the method further comprises performing an action associated with the cascaded trigger, if the value of the certain statistic exceeds the set threshold.

25. The method of claim 17, wherein:

the particular scope is a database scope associated with events performed relating to a certain database.

26. The method of claim 25, wherein:

the flags associated with the database scope indicate that a log on event is associated with a context trigger;

the new event is a logon event;

an event attribute is a user identification; and

the step of firing the trigger includes executing a context trigger action which sets an initial context responsive to the user identification.

27. The method of claim 26, wherein:

access to a row in a table depends on the initial context.

28. A method for executing triggers in response to events caused by a process in a database management system, the method comprising:

maintaining metadata about a plurality of scopes, wherein each scope determines which one or more database objects may be associated with a single trigger, each scope associated with a set of one or more events, wherein a hierarchical relationship exists among the plurality of scopes, said metadata about the plurality of scopes including a hierarchy indicating the hierarchical relationship among the plurality of scopes;

maintaining metadata about each particular scope of the plurality of scopes, the metadata about each particular scope including the set of one or more events associated with the particular scope, one or more triggers associated with the particular scope, and flags that indicate which events in the set of one or more events associated with the particular scope are associated with at least one trigger;

associating the process with a first scope of the plurality of scopes;

loading into a private cache for the process the flags for the first scope; and

loading into the private cache the flags for a second scope of the plurality of scopes, wherein the second scope occurs at a higher level in the hierarchy than a level in the hierarchy at which the first scope occurs.

29. The method of claim 28, wherein the first scope and the second scope form a branch.

30. The method of claim 28, wherein:

a schema-actor scope of the plurality of scopes is associated with events performed by any process initiated by a certain user; and

a schema-owner scope of the plurality of scopes is associated with events performed on any database object owned by the certain user associated with the schema.

31. A computer-readable medium bearing instructions arranged to cause one or more processors to perform:

receiving data that indicates a selected scope for a trigger, wherein the selected scope for the trigger is selected from a plurality of scopes of database management, wherein a first scope of the plurality of scopes consists of a single database object of a particular type and a second scope of the plurality of scopes includes a plurality of database objects of the particular type, and wherein each scope of said plurality of scopes is associated with a set of one or more events;

receiving data that indicates a selected event that belongs to the set of one or more events associated with the selected scope; and

storing trigger metadata for said trigger, said trigger metadata including data identifying the selected scope and the selected event.

32. The computer-readable medium of claim 31 bearing instructions to cause one or more processors to further perform storing metadata about the selected scope, wherein the metadata about the selected scope includes flags that indicate which events in the set of one or more events associated with the selected scope are associated with at least one trigger.

33. A computer-readable medium bearing instructions arranged to cause one or more processors to perform:

maintaining metadata about a particular scope, wherein said particular scope determines which one or more database objects may be associated with a single trigger, and wherein said particular scope is associated with a set of one or more events, said metadata including one or more triggers associated with the particular scope;

when the process causes a new event in the set of one or more events associated with said particular scope to occur, checking flags that indicate which events in the set of events associated with said particular scope are associated with at least one trigger; and

if the flags indicate that the new event is associated with a trigger, then firing the trigger.

34. The computer-readable medium of claim 33 bearing instructions to cause one or more processors to further perform:

determining whether a condition of the trigger is in a shared cache;

if the condition is in the shared cache, then evaluating the condition; and

if the condition of the trigger is not in the shared cache, then loading the condition from the metadata about the particular scope into the shared cache, and evaluating the condition.

35. A computer-readable medium bearing instructions arranged to cause one or more processors to perform:

maintaining metadata about a plurality of scopes, wherein each scope determines which one or more database objects may be associated with a single trigger, each scope associated with a set of one or more events, wherein a hierarchical relationship exists among the plurality of scopes, said metadata about the plurality of scopes including a hierarchy indicating the hierarchical relationship among the plurality of scopes;

maintaining metadata about each particular scope of the plurality of scopes, the metadata about each particular scope including the set of one or more events associated with the particular scope, one or more triggers associated with the particular scope, and flags that indicate which events in the set of one or more events associated with the particular scope are associated with at least one trigger;

associating the process with a first scope of the plurality of scopes;

loading into a private cache for the process the flags for the first scope; and

loading into the private cache the flags for a second scope of the plurality of scopes, wherein the second scope occurs at a higher level in the hierarchy than a level in the hierarchy at which the first scope occurs.

36. A computer-readable medium bearing information for use by one or more processors that are executing one or more processes participating in database management on a certain scope, wherein the certain scope determines which one or more database objects may be associated with a single trigger, the information comprising a trigger map for the certain scope, the trigger map including a plurality of flags, each flag corresponding to a respective event of a plurality of selectable events for the certain scope, wherein a flag is on if a trigger is defined for the respective event.

37. A computer-readable medium bearing information for use by one or more processors executing one or more processes that are participating in database management on a certain scope, wherein the certain scope determines which one or more database objects may be associated with a single trigger, the information comprising:

a field indicating a number of triggers in a cache; and

one or more trigger specification areas, each trigger specification area including data that indicates an event of a plurality of selectable events associated with the certain scope upon which event a trigger is fired.

38. The computer-readable medium of claim 37, wherein the trigger specification area further includes data that indicates conditions under which a trigger action is executed.

39. The computer-readable medium of claim 37, wherein the trigger specification area further includes data that indicates actions performed by the trigger if trigger conditions are satisfied.

40. A method for creating triggers, the method comprising:

receiving data that indicates a selected scope for a trigger;

wherein the selected scope for the trigger is selected from a plurality of scopes of database management;

wherein each scope of the plurality of scopes is associated with a set of one or more events;

wherein a first scope of the plurality of scopes is associated with a first set of events;

wherein a second scope of the plurality of scopes is associated with a second set of events;

wherein at least one event in said first set of events is not included in the second set of events;

receiving data that indicates a selected event that belongs to the set of one or more events associated with the selected scope; and

storing trigger metadata for said trigger, said trigger metadata including data identifying the selected scope and the selected event.