Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:
Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?
Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).
I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.
(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,o r DELETE from the SYSIBM.SYSTABLE S
and SYSIBM.SYSCOLUM NS and SYSIBM.SYSCOLPR OPERTIES and SYSIBM.SYSOPTIO NS
and SYSIBM.SYSCONST DEP)
What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:
1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.
Is there an easier way of doing this?
Thanks,
Keith Culpepper
McAleer Solutions
5  5616 
Given that you do not expect these changes to occur many times a day then
the DB2 auditor might be what you need.
Do a db2audit -h and you'll find that you can audit for
OBJMAINT which tracks who, what, where, when DDL is run.
It will show the statement used and so on.
Mind, you should set the status to both so you could see who is successful
as well as who fails.
HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<ke************ *@gmail.com> a écrit dans le message de news: 11************* *********@e56g2 00...legr oups.com... Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:
Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?
Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).
I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.
(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,o r DELETE from the SYSIBM.SYSTABLE S
and SYSIBM.SYSCOLUM NS and SYSIBM.SYSCOLPR OPERTIES and SYSIBM.SYSOPTIO NS
and SYSIBM.SYSCONST DEP)
What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:
1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.
Is there an easier way of doing this?
Thanks,
Keith Culpepper
McAleer Solutions
Ok, I forgot to mention that I'm also new to DB2. MSSQL, different
story...
Ok, I guessed that I was supposed to run db2audit -h in the command
line processor but that didn't work. Not real sure how I'm supposed to
so what you recommended. Also, reason I would like to write the
changes either to an output file or to a table is so that we can
archive them and track them back to certain version numbers...
So, suggestions?
Thanks,
Keith
Ok, got the db2audit to work (sortof) but that's not quite what I want.
Before you extract the audit data to a .log file, it has to store that
information somewhere, right? Can I query this information instead of
extracting it? Or can I setup a trigger to query it?
Thanks,
Keith
ke************* @gmail.com wrote: Ok, got the db2audit to work (sortof) but that's not quite what I want.
Before you extract the audit data to a .log file, it has to store that
information somewhere, right? Can I query this information instead of
extracting it? Or can I setup a trigger to query it?
No and No.
You don't appear to be doing this for auditing. Looks more like change
management from a development point of view.
Take a look at the Rational side of the IBM house. (Like Rational
Application Developer etc...)
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Hi All,
I unload the syscolumns table daily
then do a delta compare (good old mf cobol) - and write the delta
changes to a db2 table
Bill
<ke************ *@gmail.com> wrote in message
news:11******** **************@ e56g2000cwe.goo glegroups.com.. . Can anyone please provide some assistance with a trigger that I need to
develop. Here is the situation:
Our program updates depend on database updates. If a client receives
the program update and the database hasn't been updated then it's a
huge mess. No problem right? Just document the changes to the
database that you made and then send out a SQL script that will update
the database with the program update right?
Well, I don't trust anyone here to document anything, so I wanted to
automate the tracking of the changes made to the database. So, I want
to create a table that stores changes made to any part of the database
(create table, drop table, rename table, create column, drop column,
column reorder, change column data type/length/precision).
I am very new to triggers and do not know all I need to know about them
to create what I want so that's why I need someone's help.
(I do not know if DB2 tracks changes somewhere, only place I would know
where to look is on a INSERT,UPDATE,o r DELETE from the SYSIBM.SYSTABLE S
and SYSIBM.SYSCOLUM NS and SYSIBM.SYSCOLPR OPERTIES and SYSIBM.SYSOPTIO NS
and SYSIBM.SYSCONST DEP)
What I need is a trigger that will work one of two ways, if a change
was made to the database then the trigger would either:
1) write a line of text to a txt file or xml file explaining the change
or
2) insert a record into a table that I create so that I can select from
it to view the changes.
Is there an easier way of doing this?
Thanks,
Keith Culpepper
McAleer Solutions This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Trevor Fairchild |
last post by:
I am trying to create a very minimal auditing system for a series of
databases.
I am in the process of writing Update triggers for 5 Tablse. I will
write a trigger for each table-the trigger's function will be to
INSERT a row into my MasterChanges table everytime ANY data is changed
in each of the 5 tables.
I have set up MasterChanges to capture the following:
what Table the change was made in
|
by: Lauren Quantrell |
last post by:
I have never written a trigger before and now am seeing the light. Is
there a way to write a trigger so that if a user changes any column in
a single row on one table then the trigger will write the value of
this (these) rows to a second table. I don't want the unchanged
columns, just the changed columns (with column names...)
Thanks,
lq
|
by: Martin |
last post by:
Hello,
I'm new with triggers and I can not find any good example on how to
do the following:
I have two tables WO and PM with the following fields:
WO.WONUM, VARCHAR(10)
WO.PMNUM, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
|
by: Bill Smith |
last post by:
The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query against a
different table (that uses the LIKE predicate) but cannot get around the
SQL0132 error .
I have tried the hex notation after the LIKE such as (without the
quotes)...
" where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...
|
by: steven |
last post by:
Does anyone know how to do the following. I'm trying to mimic
replication with triggers.
I have 2 databases, each have these 2 tables.
1.
USERS
ID int
NAME varchar(20)
| | |
by: Ots |
last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app.
I have an Audit trigger that logs changes to tables. I want to apply
this trigger to many different tables. It's the same trigger, with the
exception of the table name. I could manually change the table name in
the trigger and create it, over and over, but I'd like to automate
this - by iterating through the collection of tables and passing the
tablename to something that...
|
by: toughguy |
last post by:
Hi Friends,
Iam Leela from India.
I want to track when a Trigger defined on a particular table is run in MS SQL 2000.
I have a table on which i have trigger for insertion, update, deletion defined in MS SQL table properties itself.
Using SQL Profiler I could see that a delete statement is executed on the table but i couldn't see track the corresponding trigger in SQL profiler.. Is there anyway i can track the triggers??
Thanks a...
|
by: John Hopfield |
last post by:
Hi to all,
Can you make a (simple) example about logging table changes into a
"log" table?
It is possible using triggers?
thank you
JH
|
by: ChrisC |
last post by:
Hello,
I am attempting to create a trigger to keep track of changes to a
table, mainly about specific changes to the data in the table. I had
hoped that triggers defined as for each row would actually run as each
row was deleted, but it appears that it is fired, once for each row,
after the entire delete statement has completed. Is this expected?
Here is some DDL/DML that will demonstrate what I'm trying to do - and
what the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
