update trigger

Special Forums UNIX and Linux Applications update trigger

09-17-2010

Registered User

1, 0

Join Date: Sep 2010

Last Activity: 17 September 2010, 10:03 AM EDT

Posts: 1

Thanks Given: 0

Thanked 0 Times in 0 Posts

update trigger

hi all,

i hope i am posting this /beginner) question in the right forum:
i want to create an update trigger, which rolls back a transaction if a record of a table is updated. the table has - amongst others - a field 'statusid' - if a record in this table has the statusid X and it is attempted to update this field to Y, the trigger should rollback the transaction.

thanks for every hint or advise in advance!
k

---------- Post updated at 09:03 AM ---------- Previous update was at 08:23 AM ----------

I was able to help myself

kalinkula

View Public Profile for kalinkula

Find all posts by kalinkula

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To trigger script on particular instance

Hi Guys, I have a main_script.sh which runs every day and scheduled in crontab. in the main script i read data from config file test.config apple mango orange main_script.sh for i in `cat test.config` do if then echo 'Apple'

2. Shell Programming and Scripting

Help not trigger the trap

mainpid=$$ (trap "echo timeout" SIGTERM SIGKILL SIGINT; sleep 5; kill $mainpid) & watchdogpid=$! sleep 10 #do something kill $watchdogpid I design a timeout function script I don't see the echo "timeout" is on the screen

3. Programming

MYSQL - trigger update on record insert or update

Right I have a MYSQL database with table1 with 3 columns, colA, colB and colC. I want to combine the data in the 3 columns into a 4th column names col_comb. Here's the SQL command that works: UPDATE table1 SET `col_comb` = CONCAT( `colA` , ' - ', `colB` , ', ', `colC` ); So now I want this...

4. OS X (Apple)

AC to DC trigger pulse for AudioScope.sh.

Hi all... Has _below_ ever been done in UNIX shell scripting before? (I have done this easily in Python but this is using purely the shell.) The DEMO version IS built and has been tested. Pre-amble... I now need at least one control pulse for the AudioScope.sh when in PURELY audio I/O mode,...

5. Programming

MYSQL - trigger to track changes to fields on update

So I have a php web application that allows updating of records. I would like to track the changes of only the fields that have changed. There are plenty of how to's that involve recreating the original row in a separate table with an additional time/date stamp column but I think this is a big...

6. Shell Programming and Scripting

Trigger functionality in Unix

Hi, I want a script , which searches the log for the term/phrase "JFSnapshotService::systemSnapshot: Starting data capture. This may take awhile depending upon system workload." and if there is some logging like this, it has to mail me that this data capture process is happening., Below is...

7. Shell Programming and Scripting

Update trigger for unix file (solaris)

Hello, from log error file of process that i's updating in append mode, i need to capture the new entries for every day. How i can know and save only the new errors? This it's a sample of error log file (oas report server engine) thanks and regards Fran

8. HP-UX

Control-M Job trigger

Hi, I'm totally new in Control-M and I really need some help. Here's my problem. I need to make job X from a server trigger job Y from a different server where job X must receive a confirmation first before execution. :confused: I really have no idea on how to do that so can you please give...

9. Shell Programming and Scripting

Trigger with condition

If test.ksh is successful then I have a sequence of script which needs to execute automatically. Is it possible to capture the return code to execute the next script automatically? what is better way of doing this.

10. Shell Programming and Scripting

Trigger Enter

Hello, I need to trigger every time enter has been clicked while some one on terminal i tried to googleit but with out result any idea ?? thanks in advance

LEARN ABOUT DEBIAN

parse::dia::sql::output::sqlite3

Parse::Dia::SQL::Output::SQLite3(3pm)			User Contributed Perl Documentation		     Parse::Dia::SQL::Output::SQLite3(3pm)

NAME

       Parse::Dia::SQL::Output::SQLite3 - Create SQL for SQLite version 3.

SYNOPSIS

	   use Parse::Dia::SQL;
	   my $dia = Parse::Dia::SQL->new(file => 'foo.dia', db => 'sqlite3');
	   print $dia->get_sql();

DESCRIPTION

       This sub-class creates SQL for the SQLite database version 3.

   new
       The constructor.

       Object names in SQLite have no inherent limit. 60 has been arbitrarily chosen.

   _get_create_table_sql
       Generate create table statement for a single table using SQLite syntax:

       Includes class comments before the table definition.

       Includes autoupdate triggers based on the class comment.

       autoupdate triggers

       If the class comment includes a line like:

       <autoupdate:foo/>

       Then an 'after update' trigger is generated for this table which executes the statement foo for the updated row.

       Examples of use include tracking record modification dates ("<autoupdate:dtModified=datetime('now')/">) or deriving a value from another
       field ("<autoupdate:sSoundex=soundex(sName)/">)

   get_schema_drop
       Generate drop table statments for all tables using SQLite syntax:

	 drop table {foo} if exists

   get_view_drop
       Generate drop view statments for all tables using SQLite syntax:

	 drop view {foo} if exists

   _get_fk_drop
       Drop foreign key enforcement triggers using SQLite syntax:

	 drop trigger {foo} if exists

       The automatically generated foreign key enforcement triggers are:

       See "_get_create_association_sql" for more details.

       constraint_name_bi_tr
       constraint_name_bu_tr
       constraint_name_buparent_tr
       constraint_name_bdparent_tr

   _get_drop_index_sql
       drop index statement using SQLite syntax:

	 drop index {foo} if exists

   get_permissions_create
       SQLite doesn't support permissions, so supress this output.

   get_permissions_drop
       SQLite doesn't support permissions, so supress this output.

   _get_create_association_sql
       Create the foreign key enforcement triggers using SQLite syntax:

	 create trigger {fkname}[_bi_tr|_bu_tr|_bdparent_tr|_buparent_tr]

       Because SQLite doesn't natively enforce foreign key constraints (see <http://www.sqlite.org/omitted.html>), we use triggers to emulate this
       behaviour.

       The trigger names are the default contraint name (something like child_table_fk_child_fkcolumn) with suffixes described below.

       {constraint_name} is the name of the association, either specified or generated.
       {child_table} is the name of the dependent or child table.
       {child_fkcolumn} is the field in the dependent table that hold the foreign key.
       {parent_table} is the name of the parent table.
       {parent_key} is the key field of the parent table.

       Before insert - Dependent Table

       constraint_name_bi_tr

       Before insert on the child table require that the parent key exists.

	 create trigger {constraint_name}_bi_tr before insert on {child_table}
	   for each row
	     begin
	       select
		 raise(abort, 'insert on table {child_table} violates foreign key constraint {constraint_name}')
		 where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
	     end;

       Before update - Dependent Table

       constraint_name_bu_tr

       Before update on the child table require that the parent key exists.

	 create trigger {constraint_name}_bu_tr before update on {table_name}
	   for each row
	     begin
	       select raise(abort, 'update on table {child_table} violates foreign key constraint {constraint_name}')
	       where new.{child_fkcolumn} is not null and (select {parent_key} from {parent_table} where {parent_key}=new.{child_fkcolumn}) is null;
	     end;

       Before update - Parent Table

       constraint_name_buparent_tr

       Before update on the primary key of the parent table ensure that there are no dependent child records.  Note that cascading updates don't
       work.

	 create trigger {constraint_name}_buparent_tr before update on {parent_table}
	   for each row when new.{parent_key} <> old.{parent_key}
	     begin
	       select raise(abort, 'update on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
	       where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
	     end;

       Before delete - Parent Table

       constraint_name_bdparent_tr

       The default behaviour can be modified through the contraint (in the multiplicity field) of the association.

       Default (On Delete Restrict)

       Before delete on the parent table ensure that there are no dependent child records.

	 create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
	   for each row
	     begin
	       select raise(abort, 'delete on table {parent_table} violates foreign key constraint {constraint_name} on {child_table}')
	       where (select {child_fkcolumn} from {child_table} where {child_fkcolumn}=old.{parent_key}) is not null;
	     end;

       On Delete Cascade

       Before delete on the parent table delete all dependent child records.

	 create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
	   for each row
	     begin
	       delete from {child_table} where {child_table}.{child_fkcolumn}=old.{parent_key};
	     end;

       On Delete Set Null

       Before delete on the parent table set the foreign key field(s) in all dependent child records to NULL.

	 create trigger {constraint_name}_bdparent_tr before delete on {parent_table}
	   for each row
	     begin
	       update {child_table} set {child_table}.{child_fkcolumn}=null where {child_table}.{child_fkcolumn}=old.{parent_key};
	     end;

TODO

       Things that might get added in future versions:

       Mandatory constraints

       The current foreign key triggers allow NULL in the child table. This might use a keyword in the multiplicity field (perhaps 'required') or
       could check the 'not null' state of the child fkcolumn.

       Views

       Views haven't been tested. They might already work, but who knows...

       Other stuff

       Bugs etc

perl v5.14.2							    2011-02-15				     Parse::Dia::SQL::Output::SQLite3(3pm)

UNIX and Linux Applications