× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. March 2015

Re: SQL Trigger Information

Henrik,
I already have the export tables and programs written.
What I am looking for is an easy way to get the key information when a
record in one of my files to be exported has a change to any of the
selected fields.
As I indicated, I could just set a normal trigger and have a program test
each of the before and after fields, but it would be neat if I could have
the system do that for me using SQL Trigger on the columns.

Jeff Young
Sr. Programmer Analyst

On Thu, Mar 26, 2015 at 6:37 AM, Henrik Rützou <hr@xxxxxxxxxxxx> wrote:

@Jeff

that is another matter, you will probably need som intermediate files
to collect changes from the trigger and then you need some way to
communicate the collected changes to the other system.

It all depends on how interactive you want this to be and if you want
to push or pull information between the two systems.



On Wed, Mar 25, 2015 at 8:27 PM, Jeff Young <jyoung0950@xxxxxxxxx> wrote:

First, I want to thank everyone who responded. I have received a number
of
suggestions and will keep them in mind.
Second, a clarification of the task I am attempting to solve using the
SQL
Trigger on Column.
I have another system (not IBM i) that I need to provide interface files
exported from my IBM i.
These are master files that they only want selected fields from.
While I could just create a normal trigger for Add or Update and then
code
a program to test just those fields, I thought that perhaps using an SQL
Trigger for only those columns on each file, that I could just extract
the
data I need without having to code any field change testing.


Jeff Young
Sr. Programmer Analyst

On Wed, Mar 25, 2015 at 1:09 PM, Henrik Rützou <hr@xxxxxxxxxxxx> wrote:

What you probably want to record is a collection of data about changes
in
selected field names across tables.



My experience is that this type of DB monitoring tends to start on very
few
fields and the problem is often solved by special programming. And then
it
tends to expand especially if you have IT auditors around (that
requires
procedures and documentation) or the company is listed on the stock
exchange and/or is a part of an international group.



A possible general log file may have the following fields:



File Name, File Library, File Member gives itself.



Program Name, Program Library that is a little bit tricky since you
have
to
go back in the program stack to find out who called the trigger. This
can
however be done by a system API.



Group Id, Group Value. If you want to create a picture of what is
changed
on a specific customer or a specific item data may come from a variety
of
tables – you need to record master keys such as customer number or item
number.



Row Id is a data string that identifies the actual row in the table
that
is
changed. This is just referential data.



Log Date, Log Time gives itself.



OS User Id. , Web User Id. – the OS User Id gives itself but if the
change
comes from a web system you need to record that actual user since many
web
systems runs on a generic OS User Id.



Field Name, Value Before, Value After gives itself.



Hash Value is a SHA-256 value calculated on the logged data in the row.
This will ensure that users can’t change the content of the log entry
with
tools or rather that changes are discovered.



All this information can be store in a generic log file by a Trigger
build
for each table that has fields that must be monitored. If it can be
done
with SQL and stored procedures I don’t know.



Traditional products use Journaling to do the same. The problem with
Journaling is that it is general and many changes done to files are
irrelevant so you will need a lot of journal processing to do the same.
The
trigger method is much more ‘interactive’ since it doesn’t require
subsequent batch processes.



Another thing is that you may want to notify others if data is changed.
Example: A customer calls a friend in your accounting department. The
fiend
changes the Due Date on the customers invoice to from 2015.03.25 to
2050.03.25.



There is a lot of other areas, an employee changes an item price in the
item master, enters an order to a friend with the new price and then
changes the price back in the item master.



Such events may be approved and may not, but placing a trigger on the
log
file itself makes it possible to send an e-mail notification to the
Account
Department Leader with all the relevant information the moment it is
done.



The design also makes it possible to include change history on field
level
in existing displays, a general online query or SQL queries and
periodical
reports.



So to me it is not only a question of programming but also to
construct a
practical lightweight design that is general useable without
overcomplicating things that many big and expensive DB monitors tends
to
do.



I have read Bruce Vinings article on the subject, but with all due
respect,
does he really believe that many RPG developers is able to follow him
and
make his suggestion into production code without a lot of effort, to me
it
seems a little farfetched besides he is missing a lot of practical
points.



But you are all welcome to disagree with me.

On Wed, Mar 25, 2015 at 5:54 AM, Birgitta Hauser <
Hauser@xxxxxxxxxxxxxxx

wrote:

RPGLE programs can be registered as stored procedures and then
executed
with the CALL SQL command (in an SQL trigger or any other SQL Routine
or
any environment where SQL statements can be performed).
Even though it is possible to call RPGLE directly from SQL it is much
better registering them as stored procedure for avoiding parameter
mismatch.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag
von
Jeff Young
Gesendet: Tuesday, 24.3 2015 22:11
An: Midrange Systems Technical Discussion
Betreff: Re: SQL Trigger Information

Henrik/Rob,
Thanks for the info on the SQL Trigger.
Is it possible to have the trigger call an RPGLE program when the
column
is changed?

Jeff Young
Sr. Programmer Analyst

On Tue, Mar 24, 2015 at 5:05 PM, Henrik Rützou <hr@xxxxxxxxxxxx>
wrote:

I'm actually working on a little project that records changes on
specific important fields across tables - a who did that and when -
product.

On Tue, Mar 24, 2015 at 10:00 PM, <rob@xxxxxxxxx> wrote:

A simple search of the Knowledge Center for sql trigger turned
up a
good hit. I think you want an "after" trigger such as

create trigger mytrigger
after update of MyColumn on MyTable
...

See:



http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafya
ftersql.htm?lang=en-us


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept
1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Jeff Young <jyoung0950@xxxxxxxxx>
To: Midrange Systems Technical Discussion <
midrange-l@xxxxxxxxxxxx

Date: 03/24/2015 04:31 PM
Subject: SQL Trigger Information
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



I have a file that I would like to setup a trigger for when
selected
fields are changed or a record is added and have it call an RPGLE
program.

Can I do this with SQL?
If so, does anyone have an example to share?

System is at V6R1M1.

TIA


Jeff Young
Sr. Programmer Analyst
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/midrange-l.




--
Regards,
Henrik Rützou

http://powerEXT.com <http://powerext.com/>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take
a moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please
take
a
moment to review the archives at
http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




--
Regards,
Henrik Rützou

http://powerEXT.com <http://powerext.com/>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.




--
Regards,
Henrik Rützou

http://powerEXT.com <http://powerext.com/>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.