Posted by This post is focused on WebSphere Commerce, but may have some tidbits for those new to triggers too.
Starting Point
In WebSphere Commerce, it is not unusual for you to have to add custom staging triggers. It’s not that difficult, and there’s a good example in the Info Center, but I was doing it, so I thought I’d write a post on it. I always start by going to the info center and copying syntax from here:
There are a couple of reasons I do it this way instead of copying it from some other triggers that I’ve already created or that already exist in the database. First, it’s not uncommon to modify triggers in the database, so in this case, I’m ensuring that my new trigger syntax is based on plain-vanilla triggers. If you’re doing anything fancy with filters, you may actually want to go the other direction and copy from existing triggers that populate the filter so as to copy that part of the syntax too. Another reason I copy from the info center is that I know then I’m copying insert, update, and delete triggers for the catgroup table and when I’m done with my modifications, I go back and cat my syntax for catgroup to see if there’s anywhere I forgot to change something.
When altering the default syntax to meet your needs, there’s a list of things you need to change.
- Trigger name. I know the info center tells us to use names starting with ISTG, USTG, and DSTG, but I find it much more useful on DB2 to start the trigger names with STAG like the base staging triggers, but with the last 4 digits in the 9,000 range. So my first custom trigger is STAG9000, and my second is STAG9001, and so on. This keeps us out of the range that Commerce itself is normally using. This allows me to catch all staging triggers with “where trigname like ‘STAG%'”
- Table name in the second line – after “AFTER {INSERT|UPDATE|DELETE] ON”
- Table name in the third line of the values clause – table name here should always be lower case, per Commerce’s standards
- Name of the primary key in the 6th through last lines of the values clause – the number of line is going to depend both on whether it is a DELETE trigger or not and how many columns there are in the primary key. You WILL have to add more lines to the default syntax, both in the values clause and in the column list to handle a primary key with more than one column.
You will, of course, need 3 triggers for each table you are fully adding into stagingprop – an Insert trigger, an Update trigger, and a Delete trigger.
Examples
In the work I’ve been doing recently, I needed to add staging triggers for:
- stloc
- stlocattr
- stlocds
- stlffmrel
Here’s the syntax I used to accomplish that:
connect to wcd01#
set current schema wscomusr#
Create trigger STAG9000
AFTER INSERT ON stloc REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stloc'
, 'I'
, 1
, 'stloc_id'
, N.stloc_id
, N.stloc_id
);
END#
Create trigger STAG9001
AFTER UPDATE ON stloc REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stloc'
, 'U'
, 1
, 'stloc_id'
, O.stloc_id
, N.stloc_id
);
END#
Create trigger STAG9002
AFTER DELETE ON stloc REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stloc'
, 'D'
, 1
, 'stloc_id'
, O.stloc_id
);
END#
Create trigger STAG9003
AFTER INSERT ON stlocattr REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocattr'
, 'I'
, 1
, 'stlocattr_id'
, N.stlocattr_id
, N.stlocattr_id
);
END#
Create trigger STAG9004
AFTER UPDATE ON stlocattr REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocattr'
, 'U'
, 1
, 'stlocattr_id'
, O.stlocattr_id
, N.stlocattr_id
);
END#
Create trigger STAG9005
AFTER DELETE ON stlocattr REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocattr'
, 'D'
, 1
, 'stlocattr_id'
, O.stlocattr_id
);
END#
Create trigger STAG9006
AFTER INSERT ON stlocds REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocds'
, 'I'
, 1
, 'stlocds_id'
, N.stlocds_id
, N.stlocds_id
);
END#
Create trigger STAG9007
AFTER UPDATE ON stlocds REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocds'
, 'U'
, 1
, 'stlocds_id'
, O.stlocds_id
, N.stlocds_id
);
END#
Create trigger STAG9008
AFTER DELETE ON stlocds REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlocds'
, 'D'
, 1
, 'stlocds_id'
, O.stlocds_id
);
END#
Create trigger STAG9009
AFTER INSERT ON stlffmrel REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlffmrel'
, 'I'
, 1
, 'stlffmrel_id'
, N.stlffmrel_id
, N.stlffmrel_id
);
END#
Create trigger STAG9010
AFTER UPDATE ON stlffmrel REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
, stgnkey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlffmrel'
, 'U'
, 1
, 'stlffmrel_id'
, O.stlffmrel_id
, N.stlffmrel_id
);
END#
Create trigger STAG9011
AFTER DELETE ON stlffmrel REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO staglog
( stgrfnbr
, stgstmp
, stgtable
, stgop
, stgmenbr
, stgkey1name
, stgokey1
)
VALUES (
NEXTVAL FOR STAGESEQ
, CURRENT TIMESTAMP
, 'stlffmrel'
, 'D'
, 1
, 'stlffmrel_id'
, O.stlffmrel_id
);
END#
connect reset#
A few things to notice about this syntax:
- At the beginning, I added a connect statement. This is only needed once per file.
- At the beginning, I also added a “set current schema” statement. This is required if you are (as you should be) running these commands from an id other than the one Commerce uses to access the database.
- Every statement ends with a #. Something like this is required for triggers, as the default terminator, ;, is required to be within the statements. To execute a file with these terminators, you would use something like this at the command line:
-
db2 -td# -vf jira_3634_stloc_staging_triggers.sql |tee jira_3634_stloc_staging_triggers.sql.out
You also want to make sure that you review the output file to ensure that each and every command was successful. I find this syntax helpful for that:
cat jira_3634_stloc_staging_triggers.sql.out |grep SQL |grep -v DB20000I |grep -v "MODE DB2SQL" |grep -v "SQL authorization ID"
Don’t forget you’ll also need records in stgmertab and stgsitetab for stagingprop to actually process the records that these triggers insert into staglog. The info center tells you that the table must be in stgmertab OR stgsitetab, but every time I’ve added one, I’ve had to place it in both before stagingprop will pick it up. That task is relatively easy. Most of the time, you want the tables to go at the end, and generally we go with jumps of 5 in the tabnbr to allow for any future additions.
In this case, I used:
> db2 connect to wcd01
Database Connection Information
Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = DB2INST1
Local database alias = WCD01
> db2 "select max(tabnbr) from wscomusr.stgmertab"
1
-----------
4340
1 record(s) selected.
> db2 "select max(tabnbr) from wscomusr.stgsitetab"
1
-----------
1300
1 record(s) selected.
> db2 "insert into wscomusr.stgmertab (TABNBR, TABNAME) VALUES (4345, 'stloc'), (4350, 'stlocattr'), (4355, 'stlocds'), (4360, 'stlffmrel')"
DB20000I The SQL command completed successfully.
> db2 "insert into wscomusr.stgsitetab (TABNBR, TABNAME) VALUES (1305, 'stloc'), (1310, 'stlocattr'), (1315, 'stlocds'), (1320, 'stlffmrel')"
DB20000I The SQL command completed successfully.
Altering Commerce Schema Files
After you have successfully created and tested your triggers, you should actually go out and add your changes to the Commerce schema files. This is actually the step most common to be missed. The reason is that if someone goes and drops and recreates the triggers (which can happen in a number of situations, including stagingcopy), then the syntax to handle these new custom triggers needs to be in the files commerce tells you to use for that. To that end, I add only the create trigger statements from above to /opt/IBM/WebSphere/CommerceServer70/schema/db2/wcs.stage.trigger.sql (creating a backup of the file first) on ALL APP SERVERS.
There’s another file that you need to alter that is sometimes forotten. It is /opt/IBM/WebSphere/CommerceServer70/schema/db2/wcs.droptrigger.sql – again, always create a backup before editing it. The lines I added to in in this example were:
-- Drop of custom triggers added by Ember Crooks 04/02/2012 DROP TRIGGER STAG9000 ; DROP TRIGGER STAG9001 ; DROP TRIGGER STAG9002 ; DROP TRIGGER STAG9003 ; DROP TRIGGER STAG9004 ; DROP TRIGGER STAG9005 ; DROP TRIGGER STAG9006 ; DROP TRIGGER STAG9007 ; DROP TRIGGER STAG9008 ; DROP TRIGGER STAG9009 ; DROP TRIGGER STAG9010 ; DROP TRIGGER STAG9011 ;
Notice here we’re back to using the semi-colon as the statement terminator.
You may notice while looking at these files that the number of the last base trigger in each is lower than what you notice in your database. This is because additional triggers are added by FixPacks, Feature Packs, or Feature enablements. This is expected and not something you need to mess with. Commerce keeps track of all those other trigger file locations. You can always go digging to look at them if you want, but it’s not a very fun expedition. They’re in locations like /opt/IBM/WebSphere/CommerceServer70/components/foundation/schema/fep2/db2
Things to Keep in Mind
If your tables have primary keys with more than one column, then the alterations to the trigger syntax are a bit more drastic. Commerce seems to be continuing to go the direction of having an artificial key for most tables – usually tablename_id – and having any natural or alternate key as simply a unique index. This simplifies things when it comes to adding custom staging triggers
If you are adding custom tables and also the triggers for them, you have to keep in mind as you build the tables that your primary key can consist of no more than 3 BIGINT and 2 VARCHAR(254) fields. I have actually seen a table developed and functionality around it developed and then had to make the developers go back and change the primary key because the original one did not fit within these restrictions.
Always remember that stagingprop cannot handle changes to the primary key.
Helpful Commerce Info Center Links:
Hi Ember,
Following your tips how to creatte triggers for custom tables everything was ok, but now, I have just one doubt. If there are 2 or more columns in the primary key, what do I need to do ? How can I define the trigger for this case?
Thank You,
Róbelis
Hi Ember,
ups..You can ignore the question.
There are others columns in the staglog tables for do reference in these cases of PK with multiple columns.
Thank you,
Róbelis
It was a tough week for me to get to comments, sorry. But your comments are important for others to see. Note that becauase of the column limitations, the primary key for stagingproped tables can consist of no more than 3 BIGINT fields and 2 VARCHAR(254) or less columns.