Fairly recently I was thinking about the development processes for the configuration of a large, off the shelf system. you know the type; CRM, ERP, TLA ;), that kind of thing. All things to all people, completely generic, no need to do any development to get it just right for you business, just a bit of configuration needed.
Only it's not just a bit of configuration, it's a lot of configuration. And with the business world the way it is, it's ongoing configuration much the same as it's ongoing development for every other bit of software we have.
So, if we're going to have a team of people continually working on configuring this system, configuring the system is basically changing the behaviour of the system, then what differentiates it from source code?
As far as I'm concerned, nothing.
When the configuration of the system goes as far as it does on the particular system (and it's not alone), then the configuration of the system has to be dealt with as if it's the source code of that system. It has to undergo the same quality checks, regression tests, audited rollout processes, version control.
The particular product I was looking at has had some functionality added to support these kinds of ideas. It has a clear migration method to get from development to test to staging to live. It supports that kind of structured, scripted rollout. But the config (development) tool can be attached straight to the live environment and be used to 'just make a quick change'. And there's nothing you can do to lock it down.
The configuration all lives in a database, so you can't just simply check the configuration in and out of version control. The development tool does has some version control integration, but it doesn't allow you to branch, tag or, most importantly, revert. Not only that, but the dev tool can be used to change any number of configuration sets, but when you flick between them the version control module you're using doesn't change. So you can check a config from one environment into the version control module of another!
So I find I have to ask the question... What's the point in having the option if it's so hopelessly crippled?
My only conclusion is that there is none!
Anyway, the process isn't completely doomed, there is a process that will allow us to make sure our release versions are version controlled and tagged, and therefore audited.
Unfortunately, since the solution means putting a single binary (rather than multiple files) into version control we loose many of the day to day benefits of version control, like granular logs of changes and the ability to diff. But hey, at least our process is auditable.
The whole way through the examination I was told by consultants that "most people don't do this" and "I've never worked on a project where people thought version control was necessary". Probably very true... But that's because a lot of the industry doesn't know what it's doing when it comes to software development.
It's a big shame, because the inclusion of the migration tools and the lip service towards integrated version control points to the fact that they've started to think about it. It's just that it's not very well thought out yet.
On day soon, the big players will wake up, provide the proper tools for version controlling their configurations and maybe then the rest of the industry will learn to use it.
Hopefully, the Google Test conference I'm attending this week will give me some ideas on how to add automated regression tesing, and plug another gap in their toolset...
Thursday, September 07, 2006
Thursday, August 31, 2006
Well I Never - Followup 1
OK, so I've managed to grab some time during the day to experiment, and I've got things to post. For now I've just got the time for this...
Turns out that William Robertson was quite right, the TO_CHAR 'too many declarations' issue has gone away (certainly by the time it reached 9.2), and I never even noticed!
Second up (also in 9.2) the first suspicion I had was quite right... the following doesn't work.
However, my second suspicion was off the mark (at least in 9.2). Almost certainly this is related to the change in behaviour to TO_CHAR described above,
Cheers to everyone who commented on the last post... it's led me to check out a few things that I might not have bothered with and I reckon I'll be looking a little deeper in the next few days. Contrived examples of where named parameter notation could go wrong are called for I think ;-)
Turns out that William Robertson was quite right, the TO_CHAR 'too many declarations' issue has gone away (certainly by the time it reached 9.2), and I never even noticed!
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:53:25 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production
SQL> SELECT TO_CHAR( 'CHARACTER' ) FROM DUAL
2 /
TO_CHAR('
---------
CHARACTER
SQL> SELECT TO_CHAR( NULL ) FROM DUAL
2 /
T
-
SQL>
Second up (also in 9.2) the first suspicion I had was quite right... the following doesn't work.
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:42:49 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production
SQL> CREATE OR REPLACE PACKAGE test_pkg IS
2 --
3 FUNCTION cannot_be_overloaded RETURN NUMBER;
4 FUNCTION cannot_be_overloaded RETURN VARCHAR2;
5 --
6 END test_pkg;
7 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
2 --
3 FUNCTION cannot_be_overloaded RETURN NUMBER IS
4 BEGIN
5 RETURN 0;
6 END cannot_be_overloaded;
7 --
8 FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
9 BEGIN
10 RETURN 'Character';
11 END cannot_be_overloaded;
12 --
13 END test_pkg;
14 /
Package body created.
SQL> SELECT test_pkg.cannot_be_overloaded FROM DUAL
2 /
SELECT test_pkg.cannot_be_overloaded FROM DUAL
*
ERROR at line 1:
ORA-06553: PLS-307: too many declarations of 'CANNOT_BE_OVERLOADED' match this
call
SQL> DECLARE
2 vn_number NUMBER;
3 vc_character VARCHAR2(100);
4 BEGIN
5 vn_number := test_pkg.cannot_be_overloaded;
6 vc_character := test_pkg.cannot_be_overloaded;
7 END;
8 /
vn_number := test_pkg.cannot_be_overloaded;
*
ERROR at line 5:
ORA-06550: line 5, column 25:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 28:
PLS-00307: too many declarations of 'CANNOT_BE_OVERLOADED' match this call
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
SQL>
However, my second suspicion was off the mark (at least in 9.2). Almost certainly this is related to the change in behaviour to TO_CHAR described above,
SQL*Plus: Release 9.2.0.1.0 - Production on Thu Aug 31 13:45:47 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Release 9.2.0.6.0 - Production
JServer Release 9.2.0.6.0 - Production
SQL> CREATE OR REPLACE PACKAGE test_pkg IS
2 --
3 FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER;
4 FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2;
5 --
6 END test_pkg;
7 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY test_pkg IS
2 --
3 FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER IS
4 BEGIN
5 RETURN pn_number;
6 END can_be_overloaded;
7 --
8 FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
9 BEGIN
10 RETURN pc_varchar;
11 END can_be_overloaded;
12 --
13 END test_pkg;
14 /
Package body created.
SQL> SELECT test_pkg.can_be_overloaded( 0 ) FROM DUAL
2 /
TEST_PKG.CAN_BE_OVERLOADED(0)
-----------------------------
0
SQL> SELECT test_pkg.can_be_overloaded( 'WORD' ) FROM DUAL
2 /
TEST_PKG.CAN_BE_OVERLOADED('WORD')
--------------------------------------------------------------------------------
WORD
SQL> SELECT test_pkg.can_be_overloaded( '100' ) FROM DUAL
2 /
TEST_PKG.CAN_BE_OVERLOADED('100')
--------------------------------------------------------------------------------
100
SQL> DECLARE
2 vn_number NUMBER;
3 vc_character VARCHAR2(100);
4 BEGIN
5 vn_number := test_pkg.can_be_overloaded( 0 );
6 vc_character := test_pkg.can_be_overloaded( 'WORD' );
7 vc_character := test_pkg.can_be_overloaded( '0' );
8 vn_number := test_pkg.can_be_overloaded( TO_NUMBER( '0' ) );
9 vn_number := test_pkg.can_be_overloaded( TO_CHAR( 0 ) );
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
Cheers to everyone who commented on the last post... it's led me to check out a few things that I might not have bothered with and I reckon I'll be looking a little deeper in the next few days. Contrived examples of where named parameter notation could go wrong are called for I think ;-)
Tuesday, August 29, 2006
Well I never
Good to be reminded that there's always something that you don't already know. And that's especially true of Oracle. I'd never suspected that some of those things are pretty fundamental, like the fact that package functions and procedures can be overloaded! I'd always assumed that since standalone functions and procedures can't, that the same was true of packages. Turns out that assumption was all wrong...
I.E.
This doesn't work:
But this does!
I'm sure there are gotchas in there, and I'm not really sure it's actually that useful (I've gone 8 years without it ;-) ), but still... how did I miss it? What else have I missed?
Update - aside: Why does Oracle allow overloading in the package, but not with standalone. I'm guessing, but probably because not allowing the standalone overload makes things like the 'DROP PROCEDURE' command a lot simpler to use (care to specify which procedure with that name to drop?)
Probably because allowing the package procedures to be overloaded seemed like a god idea to someone in Oracle ;-o
Update: Just re-reading the bulk of the post... and now I'm blogging something that I've not tested (no Oracle at home). But I reckon that the following won't work:
Any calls to the functions would be ambiguous. Surely Oracle can't choose which one to use based on the type of the variable you're going to hold the value in... that would be a nightmare bit of compiler to implement. No no no!
Also, I reckon you'd have to take care with this:
Even if the above would compile (I don't know if it would), then if you were to call the above with:
I suspect that Oracle will throw a wobbler. The parameter passed could be treated as a number or a varchar2, meaning either function could be valid for passing.
The only reason I suspect the package would compile is that the call could become non-ambiguous with:
I can see how this could get very knotty, positional parameters could make all kinds of function calls ambiguous.
Reckon I might try some experiments tomorrow...
I.E.
This doesn't work:
CREATE FUNCTION cannot_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END cannot_be_overloaded;
/
CREATE FUNCTION cannot_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END cannot_be_overloaded;
/
But this does!
CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION can_be_overloaded RETURN NUMBER;
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2;
--
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION can_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END can_be_overloaded;
--
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END can_be_overloaded;
--
END test_pkg;
/
I'm sure there are gotchas in there, and I'm not really sure it's actually that useful (I've gone 8 years without it ;-) ), but still... how did I miss it? What else have I missed?
Update - aside: Why does Oracle allow overloading in the package, but not with standalone. I'm guessing, but probably because not allowing the standalone overload makes things like the 'DROP PROCEDURE' command a lot simpler to use (care to specify which procedure with that name to drop?)
Probably because allowing the package procedures to be overloaded seemed like a god idea to someone in Oracle ;-o
Update: Just re-reading the bulk of the post... and now I'm blogging something that I've not tested (no Oracle at home). But I reckon that the following won't work:
CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION cannot_be_overloaded RETURN NUMBER;
FUNCTION cannot_be_overloaded RETURN VARCHAR2;
--
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION cannot_be_overloaded RETURN NUMBER IS
BEGIN
RETURN 0;
END cannot_be_overloaded;
--
FUNCTION cannot_be_overloaded RETURN VARCHAR2 IS
BEGIN
RETURN 'Character';
END cannot_be_overloaded;
--
END test_pkg;
/
Any calls to the functions would be ambiguous. Surely Oracle can't choose which one to use based on the type of the variable you're going to hold the value in... that would be a nightmare bit of compiler to implement. No no no!
Also, I reckon you'd have to take care with this:
CREATE OR REPLACE PACKAGE test_pkg IS
--
FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER;
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2;
--
END test_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_pkg IS
--
FUNCTION can_be_overloaded ( pn_number NUMBER ) RETURN NUMBER IS
BEGIN
RETURN pn_number;
END can_be_overloaded;
--
FUNCTION can_be_overloaded ( pc_varchar VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN pc_varchar;
END can_be_overloaded;
--
END test_pkg;
/
Even if the above would compile (I don't know if it would), then if you were to call the above with:
test_pkg.can_be_overloaded ( '100' );
I suspect that Oracle will throw a wobbler. The parameter passed could be treated as a number or a varchar2, meaning either function could be valid for passing.
The only reason I suspect the package would compile is that the call could become non-ambiguous with:
test_pkg.can_be_overloaded ( pc_varchar => '100' );
I can see how this could get very knotty, positional parameters could make all kinds of function calls ambiguous.
Reckon I might try some experiments tomorrow...
Tuesday, July 25, 2006
Version Control and the Patch Runner
Commenting on my first post on upgrading databases, someone with the moniker 'gonen' asked a question:
"How do you handle the situation where one developer is checking in code that doesn't have to be released in current release?"
It's a good question, but it's a question mainly of version control rather than the patch runner itself. In fact, whilst the question was asked against the database patch runner post, it's interesting that the question doesn't mention the word 'patch', it uses the word 'code'. This is a general question that relates to any code, documentation, or other product of software development.
But, before I answer the question I'm going to turn it round a little and ask:
'Why do you have code that you want in version control that you don't want in the next release?'.
I can see three main answers to this one:
1 - The code is useless and so there's no reason to ever release it.
This is probably the most unlikely answer, but hey, it happens.
If this is the case, why are you keeping it? Commonly such code is kept because "it might be useful in the future and I don't want to throw it away".
Well, is it really likely to be useful? If it is historic code that is already is version control, then with most VC you can remove it and then still get it back at a later date if you need to. Proper version control software will record the fact that the file was deleted at a particular point in time but make sure the version exists in the historic versions. Otherwise you wouldn't be able to re-release only versions of your software that requires that code.
If it's new code that's not used anywhere, isn't yet in version control and isn't currently useful... why on earth would you want that in your system? It'll likely never get used, and merely hang around for years to come by which time people will be confused by existence but too scared to delete it.
The answer: If you don't need it, don't check it in. If it's already checked in, remove it. In either case, delete it, you don't need it.
2 - You're trying to fix a bug in the live system, but since you've made the original live release you've done a load more development. You're not ready to release the new stuff to live yet.
Or to put it another way. There was a live version of the system was released and since then Alex has been working on bug fixes. At the same time Ben's working on the new release and he new funky functionality. A few weeks in and Ben's been checking in his new stuff, but hasn't quite finished it. Alex gets given a critical bug to fix that must ship to the customer as soon as it's fixed. She can't commit and release the head of the trunk because it contains Ben's half finished stuff.
Ok. So when you made the live release you tagged up the release version in your version control didn't you. If not, you can always go back and do it, you just need to know the date you cut the release. And from now on you'll tag up everything that you release right?
The tagged version is the version you want to make your live bug fixes against, not the version at the head of the trunk. When you release a bug fix version you only want the bug fixes to be released, never the new functionality
To do this, you create a branch from the tag and do your bug fixing there. On that branch you now have your live version plus your bug fix. This branch doesn't contain any of the changes that you've made in the trunk since the live version was released. Of course, whenever you make a bug fix in the branch you make sure that the bug fixed is made in the trunk version as well, otherwise when your customer comes off the branch the bug will reappear.
3 - You have several people working on code at the same time, some on short term work, some on longer term stuff. Therefore have incomplete work you don't want to release yet.
For example, Alice has a long running bit of work and has been checking code into VC as she goes along. She hasn't really finished the job yet. Ben has been doing the same, but has managed to complete his. The trunk now contains a mixture of complete and incomplete work.
If you have several streams of concurrent long term development and often get to points where you need to release one stream without releasing another then branches can help again.
Rather than allow Alice and Ben to work directly on the trunk, give them a branch each. Known as 'task branches', each branch exists purely for the length of that task. Once the task is complete the developer merges their branch into the trunk and then discards the branch. When the next long term task arrives another branch is created and the work for that task done there
Going back to the example above:
Alice and Ben work on their own branches. As Ben finishes his work and commits into his branch we have the following situation: Ben's work is complete on his branch. Alice's incomplete work is on her branch. The trunk doesn't contain either Alice or Ben's work.
Now Ben's finished his work on the branch he merges it onto the trunk, effectively promoting it into the general release. Ben's task branch can now be discarded. The trunk can now be released to the customer.
Note that if Alice had finished her work first, then her work hits the trunk and the customer release can contain that bit of functionality instead of Ben's. It is not important which bit of work is finished first, which branch is discarded first. There is flexibility inherent in the system.
However, in this situation there may be another underlying problem. Ask yourself the question, would it be possible and be more efficient to have a single stream of work that gets completed quickly rather than multiple streams that each take time? That could engender a team attitude as well as focus the developers, testers, project managers, and business as a whole on a single task at a time. Yep, I know, that's not always possible...
So what about the patch runner then?
The patch runner can work very nicely in this branching structure, just as long as it's coded to deal with the fact that patches should only ever be installed once.
To quote myself, from that earlier post:
Also, if the answer is in version control then the patch runner, the patch list and all the patches themselves need to be in version control. If they're not, then the fact that version control can answer your original question is itself a great reason for putting version control in place. Did you know that CVS and Subversion are both free... as is Tortoise (CVS and SVN). Download and install one of them, it'll take you all of 15 minutes to get a local version up and running and experimenting with.
Finally, if you want more information on version control then there are three books out there that you really should read. Well, you should read two out of three of them anyway...
Software Configuration Patterns - Steve Berczuk and Brad Appleton
Pragmatic Version Control using Subversion - Mike Mason
or
Pragmatic Version Control using CVS - Dave Thomas
And if you want more blog posts on version control then your man is Mike Mason... The go to guy on using Subversion in the real world.
Technorati Tags: Robert+Baillie, CVS, SVN, version+control, Oracle, upgrading, software, development, programming, database, DB, SQL, tortoise, patch
"How do you handle the situation where one developer is checking in code that doesn't have to be released in current release?"
It's a good question, but it's a question mainly of version control rather than the patch runner itself. In fact, whilst the question was asked against the database patch runner post, it's interesting that the question doesn't mention the word 'patch', it uses the word 'code'. This is a general question that relates to any code, documentation, or other product of software development.
But, before I answer the question I'm going to turn it round a little and ask:
'Why do you have code that you want in version control that you don't want in the next release?'.
I can see three main answers to this one:
1 - The code is useless and so there's no reason to ever release it.
This is probably the most unlikely answer, but hey, it happens.
If this is the case, why are you keeping it? Commonly such code is kept because "it might be useful in the future and I don't want to throw it away".
Well, is it really likely to be useful? If it is historic code that is already is version control, then with most VC you can remove it and then still get it back at a later date if you need to. Proper version control software will record the fact that the file was deleted at a particular point in time but make sure the version exists in the historic versions. Otherwise you wouldn't be able to re-release only versions of your software that requires that code.
If it's new code that's not used anywhere, isn't yet in version control and isn't currently useful... why on earth would you want that in your system? It'll likely never get used, and merely hang around for years to come by which time people will be confused by existence but too scared to delete it.
The answer: If you don't need it, don't check it in. If it's already checked in, remove it. In either case, delete it, you don't need it.
2 - You're trying to fix a bug in the live system, but since you've made the original live release you've done a load more development. You're not ready to release the new stuff to live yet.
Or to put it another way. There was a live version of the system was released and since then Alex has been working on bug fixes. At the same time Ben's working on the new release and he new funky functionality. A few weeks in and Ben's been checking in his new stuff, but hasn't quite finished it. Alex gets given a critical bug to fix that must ship to the customer as soon as it's fixed. She can't commit and release the head of the trunk because it contains Ben's half finished stuff.
Ok. So when you made the live release you tagged up the release version in your version control didn't you. If not, you can always go back and do it, you just need to know the date you cut the release. And from now on you'll tag up everything that you release right?
The tagged version is the version you want to make your live bug fixes against, not the version at the head of the trunk. When you release a bug fix version you only want the bug fixes to be released, never the new functionality
To do this, you create a branch from the tag and do your bug fixing there. On that branch you now have your live version plus your bug fix. This branch doesn't contain any of the changes that you've made in the trunk since the live version was released. Of course, whenever you make a bug fix in the branch you make sure that the bug fixed is made in the trunk version as well, otherwise when your customer comes off the branch the bug will reappear.
3 - You have several people working on code at the same time, some on short term work, some on longer term stuff. Therefore have incomplete work you don't want to release yet.
For example, Alice has a long running bit of work and has been checking code into VC as she goes along. She hasn't really finished the job yet. Ben has been doing the same, but has managed to complete his. The trunk now contains a mixture of complete and incomplete work.
If you have several streams of concurrent long term development and often get to points where you need to release one stream without releasing another then branches can help again.
Rather than allow Alice and Ben to work directly on the trunk, give them a branch each. Known as 'task branches', each branch exists purely for the length of that task. Once the task is complete the developer merges their branch into the trunk and then discards the branch. When the next long term task arrives another branch is created and the work for that task done there
Going back to the example above:
Alice and Ben work on their own branches. As Ben finishes his work and commits into his branch we have the following situation: Ben's work is complete on his branch. Alice's incomplete work is on her branch. The trunk doesn't contain either Alice or Ben's work.
Now Ben's finished his work on the branch he merges it onto the trunk, effectively promoting it into the general release. Ben's task branch can now be discarded. The trunk can now be released to the customer.
Note that if Alice had finished her work first, then her work hits the trunk and the customer release can contain that bit of functionality instead of Ben's. It is not important which bit of work is finished first, which branch is discarded first. There is flexibility inherent in the system.
However, in this situation there may be another underlying problem. Ask yourself the question, would it be possible and be more efficient to have a single stream of work that gets completed quickly rather than multiple streams that each take time? That could engender a team attitude as well as focus the developers, testers, project managers, and business as a whole on a single task at a time. Yep, I know, that's not always possible...
So what about the patch runner then?
The patch runner can work very nicely in this branching structure, just as long as it's coded to deal with the fact that patches should only ever be installed once.
To quote myself, from that earlier post:
So, in summary, you check out a given tagged version of the application to run against an arbitrary database and run the patch runner. It loads the list of patches that are applicable for that tagged version. It runs over each patch in turn and checks if it has previously ran. If it has not, then it runs the patch.
By the time it reaches the end of the list it has ran all the patches, none of them twice. You can even run the patch runner twice in succession and guarantee that the second run will not change the state of the database.
By the time it reaches the end of the list it has ran all the patches, none of them twice. You can even run the patch runner twice in succession and guarantee that the second run will not change the state of the database.
Also, if the answer is in version control then the patch runner, the patch list and all the patches themselves need to be in version control. If they're not, then the fact that version control can answer your original question is itself a great reason for putting version control in place. Did you know that CVS and Subversion are both free... as is Tortoise (CVS and SVN). Download and install one of them, it'll take you all of 15 minutes to get a local version up and running and experimenting with.
Finally, if you want more information on version control then there are three books out there that you really should read. Well, you should read two out of three of them anyway...
Software Configuration Patterns - Steve Berczuk and Brad Appleton
Pragmatic Version Control using Subversion - Mike Mason
or
Pragmatic Version Control using CVS - Dave Thomas
And if you want more blog posts on version control then your man is Mike Mason... The go to guy on using Subversion in the real world.
Technorati Tags: Robert+Baillie, CVS, SVN, version+control, Oracle, upgrading, software, development, programming, database, DB, SQL, tortoise, patch
Tuesday, July 04, 2006
Merge triggers
Had a quick puzzler at work today that I thought I'd share... and the link to Ask Tom that solved it (before I could be bothered to delve into the test case myself).
Q: Which statement level triggers fires on a MERGE statement?
A: Since it is possible for either inserts or updates to occur during the statement, and since statement level triggers always fire even when no rows are processed, both the INSERT and UPDATE triggers fire every time, regardless of whether any inserts or updates occur.
Obvious when you think about it. Trouble is, how many our YOUR statement level triggers would work properly if both sets fired at the same time? There's no reason why they shouldn't, if you code for the possibility....
Tom (and Mikito and Kevin and others) explains here.
Q: Which statement level triggers fires on a MERGE statement?
A: Since it is possible for either inserts or updates to occur during the statement, and since statement level triggers always fire even when no rows are processed, both the INSERT and UPDATE triggers fire every time, regardless of whether any inserts or updates occur.
Obvious when you think about it. Trouble is, how many our YOUR statement level triggers would work properly if both sets fired at the same time? There's no reason why they shouldn't, if you code for the possibility....
Tom (and Mikito and Kevin and others) explains here.
Subscribe to:
Posts (Atom)