List:General Discussion« Previous MessageNext Message »
From:bruce Date:December 11 2007 2:41am
Subject:trigger question...
View as plain text  
hi...

i have the following test sql/schema. i'm trying to create a trigger that
would allow an item in tbl2 to be updated, based upon values from the tbl
that's being inserted into, and the value in a 2nd tbl.element.

the sql/schema:

/*
test schema for stratalight file project
#
# b douglas
#
#3 creates the database, tbls for the project
# the tbl contains the file information for the various systems
# drives/users in thew stratalight system/environment
#
#
#
#
*/
drop database if exists jfrank;
create database jfrank;
use jfrank;


/*
	basic data tbl
*/
DROP TABLE IF EXISTS masterTestResultStartValTBL;
CREATE TABLE masterTestResultStartValTBL (
  serverStartVal int(20) not null default '0',
  serverSepVal int(20) not null default '0',
  prodVal int(10) not null default '0'
) TYPE=MyISAM DEFAULT CHARSET=latin1;


/*
	result startTBL
*/
DROP TABLE IF EXISTS masterTestResultStartTBL;
CREATE TABLE masterTestResultStartTBL (
  hostID int(5) not null default '0',
  testResultVal int(20) not null default '0',
  id int(10) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=MyISAM DEFAULT CHARSET=latin1;




DROP TABLE IF EXISTS masterHostTBL;
CREATE TABLE masterHostTBL (
  host varchar(50) default '',
  id int(15) NOT NULL auto_increment,
  PRIMARY KEY  (id)
) TYPE=MyISAM DEFAULT CHARSET=latin1;

--set @q = masterTestResultStartValTBL.serverStartVal;
--set @w = masterTestResultStartValTBL.serverSepVal;

delimiter |
create trigger mfgtst after insert on masterHostTBL

 for each row begin
        set @tmp = 55;
    insert into masterTestResultStartTBL
		set hostID = NEW.id,
            testResultVal =  88;

/*
        set @q = masterTestResultStartValTBL.serverStartVal;
        set @w = masterTestResultStartValTBL.serverSepVal;
*/
/*
		set hostID = NEW.id,
            testResultVal =  88;
*/

/*
(NEW.id-1)*masterTestResultStartValTBL.serverSepVal;
*/
 end;

|

delimiter ;

------------------------------------------
i can't seem to figure out where/when/how to get the values
in the masterTestResultStartValTBL to be used...

-->   set @q = masterTestResultStartValTBL.serverStartVal;
-->   set @w = masterTestResultStartValTBL.serverSepVal;

when i import the sql... i get an invalid table in the field..

any thoughts/pointers/questions would be appreciated.

basically,
 the app inserts a value in tbl1. i'd like the trigger to be able to then
use a value in tbl2, and then compute a final value, that would then be
inserted into tbl3.

thanks!!!


Thread
trigger question...bruce11 Dec
  • Re: trigger question...Martijn Tonies11 Dec