Thursday, September 13, 2007

Stupid things to do with triggers: Pt.42 in a series of 94

As is well known, the concept of :OLD values in an INSERT trigger makes no sense. Obviously there is no OLD record in such a scenario case. What is perhaps less well known is that Oracle will let us build an INSERT trigger which references an :OLD value.

SQL> create or replace trigger t1
2 before insert on t1
3 for each row
4 when ((old.col2 is null) or (new.col3 is null))
5 begin
6 if :old.col2 is null then
7 :new.col3 := 'COL2 is null';
8 elsif :new.col3 is null then
9 :new.col3 := 'COL3 is null';
10 end if;
11 end;
12 /

Trigger created.

SQL>

This means we can use the trigger to corrupt our own data without really trying...

SQL> insert into t1 (col1) values (1)
2 /

1 row created.

SQL> insert into t1 (col1,col2) values (99,56)
2 /

1 row created.

SQL> select * from t1
2 /
COL1 COL2 COL3
---------- ---------- --------------------------------
1 COL2 is null
99 56 COL2 is null

SQL>

Obviously it is unlikely that anybody would deliberately set out to do this. But it is the sort of mistake we might make by confusing the scope of variables between the INSERT and UPDATE events. Another good reason for having separate triggers for each event. Or indeed, if you are of the anti-trigger persuasion, another good reason why triggers should never have been invented in the first place.

Labels: , , ,

2 Comments:

Anonymous Anonymous said...

SEO, search engine optimization. Is to make your Web site or Blog search engines more popular in other search-related content, as far as possible to make your site appear in the results of the first of several. This will bring a wow power leveling lot of traffic, instead of complaining all day: Why am I the one does not see. At present, some of my traffic from search engine Baidu and is the most stable source of traffic. I almost did not do anything, so naturally things happened. If the SEO from the point of view, I probably spent the most stupid and most simple way, but really effective. If the search engine as a beauty, then what is the point of a simple way to let her eyes you see more of it? First, you havewow powerleveling enough fresh interesting. Second, you are unique and eye-catching. Third, you are indeed very interesting new connotation enough to say that the speed of your time in the first issued a message, followed by everyone from here to you, then you are a source of information. Found on the girl has great respect for the source of information, as your grasp, it would wow gold be tantamount to grasp the numerous reproduced the contents of your site. In that case, she must be approved by the Changlaikankan your station. The fastest you that she is from here you get the fastest, so her customers can also receive up-to-date search results. So, note to Mars will not do. The result is you confuse the site in a large site on Mars, found the girl simply can not tell to your face.

28 November 2008 07:00:00 GMT-8  
Blogger Jerry said...

cheap wedding gowns
discount bridal gowns
China wedding dresses
discount designer wedding dresses
China wedding online store
plus size wedding dresses
cheap informal wedding dresses
junior bridesmaid dresses
cheap bridesmaid dresses
maternity bridesmaid dresses
discount flower girl gowns
cheap prom dresses
party dresses
evening dresses
mother of the bride dresses
special occasion dresses
cheap quinceanera dresses
hot red wedding dresses

18 June 2009 09:29:00 GMT-7  

Post a Comment

<< Home