It happens a lot when I’m working with a client who has multiple versions of data in different spreadsheets, and I’m tasked with tidying it all up, standardizing formatting and importing it into a relational database.I have to start with one spreadsheet, then insert and/or update the differences from the others.Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with.
In this article I’ll give an overview of each feature, help you understand how to choose among them, and point out some things to watch out for. I assume My ISAM tables without support for transactions, with the following sample data: create table t1 ( a int not null primary key, b int not null, c int not null ) type=My ISAM; create table t2 ( d int not null primary key, e int not null, f int not null ) type=My ISAM; insert into t1 (a, b, c) values (1, 2, 3), (2, 4, 6), (3, 6, 9); insert into t2 (d, e, f) values (1, 1, 1), (4, 4, 4), (5, 5, 5);.
Recall that in My SQL, a primary key is simply a unique index named PRIMARY.
For each row inserted into SALES, the trigger makes a call to utl_to automatically send an email containing the discount code to the customer. And yes, I still use them to populate primary key columns that depend on sequences. And, despite highlighting the danger of using them for auditing, we do still maintain some legacy code that does that. Yes, I confess that the main database I work with still has a number of triggers in it, and I know that that makes me sound hypocritical. David Njoku is a software developer at Northgate Public Services.
If I was starting today, I would definitely use fine grained auditing instead, but we can’t rewrite the past, at least not cheaply. But it is 15 years old, and sometimes it’s too much trouble to erase the fingerprints of past developers. His tools of choice are Oracle technologies and he has over a decade of experience building applications with Oracle Forms, Oracle Application Development Framework and Oracle Application Express.
But, you may be wondering, why are triggers so hated? They can be hundreds of lines long and contain convoluted conditions.
Many developers dislike them because they fire silently in the background, seeming to live just beyond the fingertips of their control.
For Example: The price of a product changes constantly.
It is important to maintain the history of the prices of the products.
Any data that violates any unique index will cause the same problem. For example, I might export some data to a spreadsheet, send it to a client, and the client might update or add some data and return the spreadsheet to me.
That’s a terrible way to update data, but for various reasons, I’m sure many readers have found themselves in a similar situation.
And it’s pretty unanimous – from luminaries like Tom “Triggers are evil” Kyte to people like this dude (“Triggers are powerful – and much despised”). To answer your question, let me start by quoting myself again: Simple triggers to populate primary key columns from sequences are pretty uncontroversial; however, there is no upper limit to their potential complexity.