|
|
|
|
|
|
| Author |
Message |
rmcgorman@gmail.com
*nix forums beginner
Joined: 29 May 2006
Posts: 6
|
 Posted: Thu Jul 13, 2006 9:04 pm Post subject:
Newbie Trigger Question - Revisited
|
|
|
I started a thread back in late May. Shortly after posting I got
caught up in another project and didn't get back to the "trigger" work
until today. I tried to reply to the original thread but it won't
allow replies to threads older than 30 days. Here's a link to the
original thread (hope it works):
http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/6f3e06c3cd58af58/9c4c8b281c773574?q=trigger&rnum=11#9c4c8b281c773574
Sorry for taking so long to respond.
Serge had provided the syntax to convert a Sybase trigger to DB2.
Here's his last post.
| Quote: | create trigger AAA_ins_trg
on AAA for insert as
update AAA
set bbb = L.bbb
from AAA A,
LLL L
where A.aaa = L.aaa
return
|
Huh? Why don't you refer to the changed rowset at all?
CREATE TRIGGER AAA_ins_trg
AFTER INSERT ON AAA FOR EACH STATEMENT
MERGE INTO AAA USING LLL L ON A.aaa = L.aaa
WHEN MATCHED THEN A.bbb = L.bbb
(Makes no sense to me....)
Could it be that LLL is the "INSERTED" table?
In this case:
CREATE TRIGGER AAA_ins_trg
BEFORE INSERT ON AAA REFERENCING NEW AS n FOR EACH ROW
SET n.bbb = .....
Cheers
Serge
To answer the question "Huh? Why don't you refer to the changed rowset
at all?"
Sybase provides two virtual tables inside a trigger: "inserted" and
"deleted". The tables are read-only (being virtual), and there's no
way to access the updated rowset short of updating it again.
I tried your example (our server is UDB 8.0 on AIX) and got an error.
Here's the exact SQL I ran and the error I received. I did add the T1
alias, assuming that you have missed it in your example.
CREATE TRIGGER TBLPLPS_ins_trg
AFTER INSERT ON TBLPLPS FOR EACH STATEMENT
MERGE INTO TBLPLPS T1 USING TBLTEAMNAMES T2 ON T1.HOME_name =
T2.TEAM_ABBR
WHEN MATCHED THEN T1.HOME_DESC = T2.TEAM_NAME ;
"SQL0104N An unexpected Token "THEN" was found following 'M_ABBR WHEN
MATCHED"
I tried a number of variations but none worked. Is there an obvious
error?
Thanks. |
|
| Back to top |
|
 |
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Fri Jul 14, 2006 3:29 pm Post subject:
Re: Newbie Trigger Question - Revisited
|
|
|
rmcgorman@gmail.com wrote:
| Quote: | I started a thread back in late May. Shortly after posting I got
caught up in another project and didn't get back to the "trigger" work
until today. I tried to reply to the original thread but it won't
allow replies to threads older than 30 days. Here's a link to the
original thread (hope it works):
http://groups.google.com/group/comp.databases.ibm-db2/browse_frm/thread/6f3e06c3cd58af58/9c4c8b281c773574?q=trigger&rnum=11#9c4c8b281c773574
Sorry for taking so long to respond.
Serge had provided the syntax to convert a Sybase trigger to DB2.
Here's his last post.
create trigger AAA_ins_trg
on AAA for insert as
update AAA
set bbb = L.bbb
from AAA A,
LLL L
where A.aaa = L.aaa
return
Huh? Why don't you refer to the changed rowset at all?
CREATE TRIGGER AAA_ins_trg
AFTER INSERT ON AAA FOR EACH STATEMENT
MERGE INTO AAA USING LLL L ON A.aaa = L.aaa
WHEN MATCHED THEN A.bbb = L.bbb
(Makes no sense to me....)
Could it be that LLL is the "INSERTED" table?
In this case:
CREATE TRIGGER AAA_ins_trg
BEFORE INSERT ON AAA REFERENCING NEW AS n FOR EACH ROW
SET n.bbb = .....
Cheers
Serge
To answer the question "Huh? Why don't you refer to the changed rowset
at all?"
Sybase provides two virtual tables inside a trigger: "inserted" and
"deleted". The tables are read-only (being virtual), and there's no
way to access the updated rowset short of updating it again.
I tried your example (our server is UDB 8.0 on AIX) and got an error.
Here's the exact SQL I ran and the error I received. I did add the T1
alias, assuming that you have missed it in your example.
CREATE TRIGGER TBLPLPS_ins_trg
AFTER INSERT ON TBLPLPS FOR EACH STATEMENT
MERGE INTO TBLPLPS T1 USING TBLTEAMNAMES T2 ON T1.HOME_name =
T2.TEAM_ABBR
WHEN MATCHED THEN T1.HOME_DESC = T2.TEAM_NAME ;
"SQL0104N An unexpected Token "THEN" was found following 'M_ABBR WHEN
MATCHED"
I tried a number of variations but none worked. Is there an obvious
error?
Try WHEN MATCHED THEN UPDATE SET T1.HOME_DESC = T2.TEAM_NAME |
But here is what you REALLY want:
CREATE TRIGGER TBLPLPS_ins_trg
BEFORE INSERT ON TBLPLPS REFERNCEING NEW AS N FOR EACH ROW
SET N.HOME_DESC = (SELECT TEAM_NAME FROM TBLTEAMNAMES T2
WHERE N.HOME_name = T2.TEAM_ABBR);
That trigger will fly 
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/ |
|
| Back to top |
|
|
4.spam@mail.ru
*nix forums beginner
Joined: 14 Jul 2005
Posts: 49
|
| Posted: Fri Jul 14, 2006 3:32 pm Post subject:
Re: Newbie Trigger Question - Revisited
|
|
|
Hello.
Serge has already given sample like this:
----
create trigger tai_aaa
BEFORE INSERT ON AAA
REFERENCING NEW AS n
FOR EACH ROW
SET n.b = (select b from LLL where a=n.a)
@
----
Such trigger doesn't do the job?
Sincerely,
Mark B. |
|
| Back to top |
|
|
rmcgorman@gmail.com
*nix forums beginner
Joined: 29 May 2006
Posts: 6
|
| Posted: Mon Jul 17, 2006 7:27 pm Post subject:
Re: Newbie Trigger Question - Revisited
|
|
|
Thanks Serge. That worked. I have one more question if you don't
mind.
Here's a trigger that exists in my database.
CREATE TRIGGER "[tblPicks]_trig" NO CASCADE
BEFORE INSERT ON tblPicks
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF NEW.cancelled IS NULL THEN
SET NEW.cancelled = MS7.TO_BIT((0));
END IF;
END
I need to add logic to this trigger that's similar to what we added in
the earlier trigger (that you already corrected). Here's the logic I
want to add:
SET T1.PROD_GROUP_TYPE_CODE = 'LOTPRPICKS',
T1.PARTICIPANT_DESC = (SELECT TEAM_NAME
FROM TBLCARDS T2, TBLTEAMNAMES T3
WHERE T3.TEAM_ABBR = T1.PARTICIPANT and
T3.SPORT_ID = T2.SPORT_ID and
T2.CARD_ID = T1.CARD_ID and
T2.LIST_NUMBER = T1.LIST_NUMBER)
I've tried adding the logic in many spots but I keep getting errors.
I'm really struggling determining what the errors are trying to tell
me. I guess that will come in time.
Any idea what the MS7.TO_BIT((0)) is in the trigger?
Thanks, again.
Richard |
|
| Back to top |
|
|
Serge Rielau
*nix forums Guru
Joined: 29 Apr 2005
Posts: 1583
|
| Posted: Mon Jul 17, 2006 7:40 pm Post subject:
Re: Newbie Trigger Question - Revisited
|
|
|
rmcgorman@gmail.com wrote:
| Quote: | Thanks Serge. That worked. I have one more question if you don't
mind.
[I] Never mind.
Here's a trigger that exists in my database.
CREATE TRIGGER "[tblPicks]_trig" NO CASCADE
BEFORE INSERT ON tblPicks
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
IF NEW.cancelled IS NULL THEN
SET NEW.cancelled = MS7.TO_BIT((0));
END IF;
END
*grmbl*That's why migrated code is always slower*grmbl* |
Please rewrite to:
| Quote: | CREATE TRIGGER "[tblPicks]_trig" NO CASCADE
BEFORE INSERT ON tblPicks
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
SET NEW.cancelled = COALESCE(NEW.cancelled, MS7.TO_BIT(0)); |
| Quote: | I need to add logic to this trigger that's similar to what we added in
the earlier trigger (that you already corrected). Here's the logic I
want to add:
Like this?
CREATE TRIGGER "[tblPicks]_trig" NO CASCADE
BEFORE INSERT ON tblPicks
REFERENCING NEW AS NEW FOR EACH ROW MODE DB2SQL
SET NEW.cancelled = COALESCE(NEW.cancelled, MS7.TO_BIT(0)), |
NEW.PROD_GROUP_TYPE_CODE = 'LOTPRPICKS',
NEW.PARTICIPANT_DESC = (SELECT TEAM_NAME
| Quote: | FROM TBLCARDS T2, TBLTEAMNAMES T3
WHERE T3.TEAM_ABBR = T1.PARTICIPANT and
T3.SPORT_ID = T2.SPORT_ID and
T2.CARD_ID = T1.CARD_ID and
T2.LIST_NUMBER = T1.LIST_NUMBER); |
| Quote: | I've tried adding the logic in many spots but I keep getting errors.
I'm really struggling determining what the errors are trying to tell
me. I guess that will come in time.
If you post the actual error we can help, otherwise we merely guess. |
| Quote: | Any idea what the MS7.TO_BIT((0)) is in the trigger?
Let me guess. The source database had some sort of BIT datatype. |
Likely MTK has translated the column to a CHAR(1) to be 'Y' or 'N' or
something like that.
MS7.TO_BIT() maps between the source input (0) and 'N'.
You could optimize that of course.
You will find many such examples in your procedures and triggers.
MTK builds an MS SQL Server _EMULATION_ of your app.
Once you get the code working and you start tuning for performance you
can grep for MS7 and start throwing out the trash. :-)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
IOD Conference
http://www.ibm.com/software/data/ondemandbusiness/conf2006/ |
|
| Back to top |
|
|
rmcgorman@gmail.com
*nix forums beginner
Joined: 29 May 2006
Posts: 6
|
| Posted: Mon Jul 17, 2006 8:33 pm Post subject:
Re: Newbie Trigger Question - Revisited
|
|
|
Serge Rielau wrote:
,,, Snip
| Quote: | I've tried adding the logic in many spots but I keep getting errors.
I'm really struggling determining what the errors are trying to tell
me. I guess that will come in time.
If you post the actual error we can help, otherwise we merely guess.
|
Actually, I meant DB2 errors in general. It seems like the errors
are pointing me to one part of the SQL when the eventual fix ends up
being elsewhere. I'm sure I had the same issue with Sybase error
messages when I first started working with it. In time it'll become
second nature, I hope. ;-)
| Quote: |
Any idea what the MS7.TO_BIT((0)) is in the trigger?
Let me guess. The source database had some sort of BIT datatype.
Likely MTK has translated the column to a CHAR(1) to be 'Y' or 'N' or
something like that.
MS7.TO_BIT() maps between the source input (0) and 'N'.
You could optimize that of course.
You will find many such examples in your procedures and triggers.
MTK builds an MS SQL Server _EMULATION_ of your app.
Once you get the code working and you start tuning for performance you
can grep for MS7 and start throwing out the trash. :-)
|
You raise an interesting point. This database and application was
written for us by a third party a number of years ago. It ran on SQL
Server 7. It was migrated to DB2 by one of our DBA's and he just used
a reverse-engineering tool to generate the DB2 SQL. Apparently, the
reference to MS7.TO_BIT compiles fine, but I wonder what happens when
an insert into this table tries to resolve the reference. It's likely
not valid. Before I add it to my trigger I'm going to do some insert
test.
Thanks, once again.
Richard |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Mon Dec 01, 2008 9:54 pm | All times are GMT
|
|
Cheap Loan | Just Holden Commodores | Advertising | Car Loan | Remortgages
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
IBM DB2
