NYCPHP Meetup

[Phil Powell]

Taken from  
http://www-css.fnal.gov/dsg/external/freeware/mysql-vs-pgsql.html

Triggers:

 MySQL:  No triggers supported now and won't be added in the future.
 Oracle8:In PL/SQL. Possibility of writing triggers reacting on the events:
         BEFORE/AFTER DELETE/UPDATE/INSERT. INSTEAD OF triggers can be 
used for updating data through views.
 PostgreSQL: Declarative rules - extension to SQL. You can specify 
SELECT, INSERT, DELETE or UPDATE
          as a rule event. INSTEAD OF rules can be used for updating 
data through views.
          Procedural triggers in PL/PGSQL, PL/TCL, PL/Perl, C. CREATE 
CONSTRAINT TRIGGER creates
          a trigger to support a constraint.
          You can specify BEFORE or AFTER on INSERT, DELETE or UPDATE as 
a trigger event.

I am having to create a cascade delete effect on table "department" that 
is configured in the following fashion:

-- TABLE CONTAINING DEPARTMENTS FOR PERSONS
create table if not exists department (
 id int not null auto_increment,
 primary key (id),
 department_name varchar(50) not null,
 department_parent_id int,
 unique_key varchar(20) not null,
 record_entered datetime
);

department_parent_id is a "foreign key constraint" (table is MyISAM thus 
no actual foreign key) set up to be recursively
referring to another record (if not null) in the table.

I am having to delete a department; if I do so, I would naturally have 
to delete all child records associated with the department.
Best approach would be to do a "cascade delete" variation (deleting all 
child records first and then parent row), however, only
MaxDB seems to support that (see 
http://www.mysql.com/documentation/maxdb/94/c38409c20611d5993d00508b6b8b11/content.htm 
and
http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html ), 
however, that seems to be not possible since my tables are MyISAM
by default.  Therefore, I am offered the possibility of a trigger.  
However, the beforementioned article indicates triggers
aren't supported in MySQL. 

What would then be your best recommendation, based upon the need and the 
schema, for me to ensure that all related records are
deleted?  I would prefer not to have to write a PHP script to have to do 
so, but is that all I'm stuck with?

Thanx
Phil

-- 
Phil Powell
Web Developer
  ADNET Systems, Inc.
  11260 Roger Bacon Drive, Suite 403
  Reston, VA  20190-5203
Phone: (703) 709-7218 x107   Cell: (571) 437-4430   FAX: (703) 709-7219
EMail:  Phillip.Powell at adnet-sys.com      AOL IM: SOA Dude