Statement-level instead-of database triggers
Techniques are disclosed relating to implementing a statement-level INSTEAD OF trigger. In one embodiment a computer system stores trigger information associated with a statement-level database trigger executable to initiate execution of at least one trigger instruction for a database instead of performing a particular database operation, on a database view, specified by a database operation statement. The computer system receives a first database operation statement specifying performance of the particular database operation on the database view and identifies a set of target rows, within the database view, targeted by the first database operation statement. In addition, the computer system generates a reference table associated with the database view, where the reference table includes rows corresponding to the target rows. The computer system executes the statement-level database trigger instead of executing the database operation statement, where executing the statement-level database trigger includes accessing the reference table.
1. A method, comprising:
storing, by a computer system, trigger information associated with a first statement-level database trigger executable to initiate execution of at least one first trigger instruction for a database instead of performing a particular database operation, on a first database view of selected data from the database, specified by a database operation statement;
receiving, by the computer system, a first database operation statement specifying performance of the particular database operation on the first database view;
identifying, by the computer system, a set of first target rows, within the first database view, targeted by the first database operation statement;
generating, by the computer system, a first reference table associated with the first database view, wherein the first reference table includes rows corresponding to the first target rows; and
executing, by the computer system, the first statement-level database trigger instead of executing, rather than before or after executing, the first database operation statement, wherein executing the first statement-level database trigger includes accessing the first reference table and wherein a single execution of the first statement-level database trigger is performed instead of execution of the first database operation statement for multiple of the first target rows.
2. The method of claim 1 , wherein a given row, in the first reference table, corresponding to a given first target row includes original data values reflecting a state of the given first target row without performing the particular database operation.
3. The method of claim 1 , wherein a given row, in the first reference table, corresponding to a given first target row includes updated data values reflecting a state of the given first target row if the particular database operation is performed.
4. The method of claim 1 , wherein a given row, in the first reference table, corresponding to a given first target row includes both original data values reflecting a state of the given first target row without performing the particular database operation and updated data values reflecting a state of the given first target row if the particular database operation is performed.
5. The method of claim 1 , further comprising:
storing, by the computer system, trigger information associated with a second statement-level database trigger executable to initiate execution of at least one second trigger instruction for the database instead of performing the particular database operation on the first database view; and
executing, by the computer system, the second statement-level database trigger instead of executing the first database operation statement, wherein executing the second statement-level database trigger includes accessing the first reference table.
6. The method of claim 1 , wherein the first database operation statement further specifies a second database view of selected data from the database, and further comprising:
identifying, by the computer system, a set of second target rows, within the second database view, targeted by the first database operation statement; and
generating, by the computer system, a second reference table associated with the second database view, wherein the second reference table includes rows corresponding to the second target rows.
7. A non-transitory computer readable medium having program instructions stored thereon that are capable of causing a computer system to perform operations comprising:
storing trigger information associated with a first statement-level database trigger executable to initiate execution of at least one first trigger instruction for a database instead of performing a particular database operation, on a first database view of selected data from the database, specified by a database operation statement;
receiving a first database operation statement specifying performance of the particular database operation on the first database view;
identifying a set of first target rows, within the first database view, targeted by the first database operation statement;
generating a first reference table associated with the first database view, wherein the first reference table includes rows corresponding to the first target rows; and
executing the first statement-level database trigger instead of executing, rather than before or after executing, the first database operation statement, wherein executing the first statement-level database trigger includes accessing the first reference table and wherein a single execution of the first statement-level database trigger is performed instead of execution of the first database operation statement for multiple of the first target rows.
8. The computer readable medium of claim 7 , wherein a given row, in the first reference table, corresponding to a given first target row includes original data values reflecting a state of the given first target row without performing the particular database operation.
9. The computer readable medium of claim 7 , wherein a given row, in the first reference table, corresponding to a given first target row includes updated data values reflecting a state of the given first target row if the particular database operation is performed.
10. The computer readable medium of claim 7 , wherein a given row, in the first reference table, corresponding to a given first target row includes both original data values reflecting a state of the given first target row without performing the particular database operation and updated data values reflecting a state of the given first target row if the particular database operation is performed.
11. The computer readable medium of claim 7 , wherein the operations further comprise:
storing trigger information associated with a second statement-level database trigger executable to initiate execution of at least one second trigger instruction for the database instead of performing the particular database operation on the first database view; and
executing the second statement-level database trigger instead of executing the first database operation statement, wherein executing the second statement-level database trigger includes accessing the first reference table.
12. The computer readable medium of claim 7 , wherein the first database operation statement further specifies a second database view of selected data from the database, and the operations further comprise:
identifying a set of second target rows, within the second database view, targeted by the first database operation statement; and
generating a second reference table associated with the second database view, wherein the second reference table includes rows corresponding to the second target rows.
13. A system, comprising:
at least one processor; and
memory having program instructions stored thereon that are executable by the at least one processor to cause the system to perform operations comprising:
storing trigger information associated with a first statement-level database trigger executable to initiate execution of at least one first trigger instruction for a database instead of performing a particular database operation, on a first target arrangement, into rows and columns, of data from the database, specified by a database operation statement;
receiving a first database operation statement specifying performance of the particular database operation on the first target arrangement;
identifying a set of first target rows, within the first target arrangement, targeted by the first database operation statement;
generating a first reference table associated with the first target arrangement, wherein the first reference table includes rows corresponding to the first target rows; and
executing the first statement-level database trigger instead of executing, rather than before or after executing, the first database operation statement, wherein executing the first statement-level database trigger includes accessing the first reference table and wherein a single execution of the first statement-level database trigger is performed instead of execution of the first database operation statement for multiple of the first target rows.
14. The system of claim 13 , wherein the first target arrangement is a database view specified by a query of the database.
15. The system of claim 13 , wherein a given row, in the first reference table, corresponding to a given first target row includes original data values reflecting a state of the given first target row without performing the particular database operation.
16. The system of claim 13 , wherein a given row, in the first reference table, corresponding to a given first target row includes updated data values reflecting a state of the given first target row if the particular database operation is performed.
17. The system of claim 13 , wherein a given row, in the first reference table, corresponding to a given first target row includes both original data values reflecting a state of the given first target row without performing the particular database operation and updated data values reflecting a state of the given first target row if the particular database operation is performed.
18. The system of claim 13 , wherein the operations further comprise:
storing trigger information associated with a second statement-level database trigger executable to initiate execution of at least one second trigger instruction for the database instead of performing the particular database operation on the first target arrangement; and
executing the second statement-level database trigger instead of executing the first database operation statement, wherein executing the second statement-level database trigger includes accessing the first reference table.
19. The system of claim 13 , wherein the first database operation statement further specifies a second target arrangement of rows and columns from the database, and the operations further comprise:
identifying a set of second target rows, within the second target arrangement, targeted by the first database operation statement; and
generating a second reference table associated with the second target arrangement, wherein the second reference table includes rows corresponding to the second target rows.
20. The system of claim 19 , wherein the operations further comprise:
storing trigger information associated with a second statement-level database trigger executable to initiate execution of at least one second trigger instruction for the database instead of performing the particular database operation on the second target arrangement; and
executing the second statement-level database trigger instead of executing the first database operation statement, wherein executing the second statement-level database trigger includes accessing the second reference table.