× 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

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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
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.