my task make trigger fire when our programmers create, alter, replace or delete triggers in database. must log changes 2 datatables made similar sys.trigger$ table , added info user made changes them. copied principles of logging existing audit capability in erp-system named galaktika or galaxy simple. however, encountered well-famous problem ora-04089: no 1 can create triggers on system tables , stuck it.
now i'm looking way gently modify trigger according database rules. here original code:
create or replace trigger mrk_altrigger$ before delete or insert or update on sys.trigger$ referencing new new old old each row declare log_rec mrk_triggers_log_header.nrec%type; begin insert mrk_triggers_log_header (dateofchange, usercode, operation, obj#) values ( sysdate, uid, case when inserting 0 when updating 1 when deleting 2 end, case when inserting or updating :new.obj# else :old.obj# end) returning nrec log_rec; if inserting or updating insert mrk_triggers_log_specif (loglink, obj#, type#, update$, insert$, delete$, baseobject, refoldname, refnewname, definition, whenclause, action#, actionsize, enabled, property, sys_evts, nttrigcol, nttrigatt, refprtname, actionlineno) values (log_rec, :new.obj#, :new.type#, :new.update$, :new.insert$, :new.delete$, :new.baseobject, :new.refoldname, :new.refnewname, :new.definition, :new.whenclause, :new.action#, :new.actionsize, :new.enabled, :new.property, :new.sys_evts, :new.nttrigcol, :new.nttrigatt, :new.refprtname, :new.actionlineno); end if; exception when others -- consider logging error , re-raise raise; end mrk_altrigger$; / i can provide mrk_triggers_log_header , mrk_triggers_log_specif ddl, think not necessary. make summary, here questions have:
- how modify above source syntax
create or replace trigger on database? - am inventing wheel doing this? there common way such things? (i noticed tables have logging option, consider debugging purposes)
any appreciated!
upd: came decision (thanks apc) better hold different versions of code in source control , record revision number in db, dream doing automatically.
"we despaired appeal our programmers' neatness boss requires there must strong , automatic way log changes. , revert them if need."
in other words, want technical fix political problem. not work. however, if have boss's support can sort out. messy.
i have been on both sides of fence, having worked developer , development dba. know bitter experience how bad can if development database - schemas, configuration parameters, reference data, etc - not kept under control. developers feel flying right now, guarantee not tracking changes make in script form . changes not reversible or repeatable, , when project reaches uat deployment fiasco (buy me beer , i'll tell stories).
so do?
privileged access
revoke access sysdba accounts , application schema accounts developers. apart else may find parts of application start rely on privileged accesses and/or hard-coded passwords, , bad things; don't want include breaches in production.
as developers have got accustomed having such access highly unpopular. why need boss's support. must have replacement approach in place, leave action until last. make no mistake, endgame.
source control
database schemas software too. built out of programs, rest of application, source code ddl , dml scripts not c# or java. these scripts can controlled in svn other source code.
how organise in source control? can tricky. recognise have 3 categories of scripts:
- schema scripts deploy objects
- configuration scripts insert reference data, manage system parameters, etc
- build scripts call other scripts in right order
managing schema scripts hardest thing right. suggest use separate scripts each object. also, have separate scripts table, indexes , constraints. means can build tables without needing arrange them in dependency order.
handling change
the temptation control create table statement (or whatever). mistake. in actuality changes schema add, drop or modify columns introduce totally new objects. store create table statement baseline, manage subsequent changes alter table statements.
one file create table , subsequent alter table commands, or separate ones? i'm comfortable having 1 script: don't mind if create table statement fails when i'm expecting table there. can confusing if others running scripts in production. have baseline script separate scripts applying changes. 1 alter script per object per time-box compromise.
changes developers consist of
- alter table script(s) apply change
- a mirrored alter table script(s) reverse change
- other scripts, e.g. dml
- change reference number (which use in svn)
because you're introducing late in day, you'll need diplomatic. make change process light , easy use. make sure check , run scripts possible. if you're responsive , things enough developers won't chafe under restricted access.
getting there
first of need establish baseline. dbms_metadata give create statements current objects. need organise them in svn , write build scripts. create toy database , right.
this may take time, remember refresh ddl scripts reflect latest statement. if have access schema comparison tool handy right now.
next, sort out configuration. know tables contain reference data, otherwise ask developers.
in toy database practice zapping database , building scratch. can use ant or hudson automate if you're feeling adventurous, @ least need shell scripts build out of svn.
making transition
this big one. announce new regime developers. boss attend meeting. remind developers inform of changes make database.
that night:
- take full export data pump
- drop application schemas.
- build application svn
- reload data - not data structures - data pump
- hopefully won't have structural issues; if developer has made changes without telling you'll know - , won't have data in table.
- make sure revoke sysdba access possible.
the developers need access set of schemas can write alter scripts. in developers don't have local personal databases or private schemas test things suggest let them have access toy database test change scripts. alternatively can let them keep application owner access, because you'll repeating trash'n'rebuild exercise on regular basis. once used idea lose changes don't tell knuckle down , start doing right thing.
last word
obviously lot of vague windbaggery, lacking in solid detail. that's politics you.
postscript
i @ a ukoug event yesterday, , attended session couple of smart chaps regdate. have product source control oracle provides interface between (say) svn , database. takes rather different approach outlined above. approach sound one. tool automates lot of things, , think might lot in current situation. must stress haven't used product think should check out - there's 28 day free trial. of course, if don't have money spend won't you.
Comments
Post a Comment