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!!!

Hi Bruce,

> 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..

Your trigger is on table "masterHostTBL", that means if you
want to get values from table "masterTestResultStartValTBL",
you need to SELECT them from that table.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com