Hello Hugo,
Thanks for your help I really appreciate the time and effort that you
give to UNKNOWN people!
I have this program that helps us track the Work Orders request for
this site. Every day we use a WO Screen to enter the routine request
from our clients; all the data goes to the workorder Table and we have
the problemcode and the woassignmntqueueid fields set as required (Not
Null Allowed). This process works perfect we do not have any problems
with this daily process.
My Problem is that this program also have a PM (Preventive Maintenance)
screen that I have to use every February to generate all the PM work
orders for the whole year; these work orders or records are also
created in the workorder table and of course the data from the daily
work orders are different from the PM work orders and that is the
reason this program doesn't populate these to fields. Also in a few
occasion during the year I have to create new PM work order for
equipment that is added or replace on this site.
When I generate the PM for the site I have to do it after hours, I
configure the database and make those two fields to allow null values
then I run update queries to populate them, and then I reconfigure the
DB so these two fields are required again. The other problem is that if
we add or replace a piece of equipment and we need a PM work order
immediately, I can't happen.
That is the main reason I need to create this trigger.
My intention is when I'm on the PM screen and run the automate
routine to Generate or Create PM work order, this trigger will pass the
data from the PM table to the workorder table
Workorder.problemcode = pm.pm1
Workorder.woassignmntqueueid = pm.pm2
PM Table
Pmnun Description Pm1 Pm2
Hvac001 Monthly A/C Unit PM HVAC MAINT
Workorder Table
Wonum Description Pmnum problemcode woassignmntqueueid
1234567 Monthly A/C Unit PM Hvac001 HVAC MAINT
I have about 500 PM records and each can have many records on the
workorder table. The PMNUM field is the key field on the PM table and
a foreign key on the workorder table.
Below is the code to create and populate the workorder and PM table.
I really appreciate all your help.
Thanks!
Martin
create table workorder (
wonum varchar (10) not null ,
parent varchar (10) null ,
status varchar (8) not null ,
statusdate datetime not null ,
worktype varchar (5) null ,
leadcraft varchar (8) null ,
description varchar (50) null ,
eqnum varchar (8) null ,
location varchar (8) null ,
jpnum varchar (10) null ,
faildate datetime null ,
changeby varchar (18) null ,
changedate datetime null ,
estdur double precision not null ,
estlabhrs double precision not null ,
estmatcost decimal(10,2) not null ,
estlabcost decimal(10,2) not null ,
esttoolcost decimal(10,2) not null ,
pmnum varchar (8) null ,
actlabhrs double precision not null ,
actmatcost decimal(10,2) not null ,
actlabcost decimal(10,2) not null ,
acttoolcost decimal(10,2) not null ,
haschildren varchar (1) not null ,
outlabcost decimal(10,2) not null ,
outmatcost decimal(10,2) not null ,
outtoolcost decimal(10,2) not null ,
historyflag varchar (1) not null ,
contract varchar (8) null ,
wopriority integer null ,
wopm6 varchar (10) null ,
wopm7 decimal(15,2) null ,
targcompdate datetime null ,
targstartdate datetime null ,
woeq1 varchar (10) null ,
woeq2 varchar (10) null ,
woeq3 varchar (10) null ,
woeq4 varchar (10) null ,
woeq5 decimal(10,2) null ,
woeq6 datetime null ,
woeq7 decimal(15,2) null ,
woeq8 varchar (10) null ,
woeq9 varchar (10) null ,
woeq10 varchar (10) null ,
woeq11 varchar (10) null ,
woeq12 decimal(10,2) null ,
wo1 varchar (10) null ,
wo2 varchar (10) null ,
wo3 varchar (10) null ,
wo4 varchar (10) null ,
wo5 varchar (10) null ,
wo6 varchar (10) null ,
wo7 varchar (10) null ,
wo8 varchar (10) null ,
wo9 varchar (10) null ,
wo10 varchar (10) null ,
ldkey integer null ,
reportedby varchar (18) null ,
reportdate datetime null ,
phone varchar (20) null ,
problemcode varchar (8) not null ,
calendar varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
actstart datetime null ,
actfinish datetime null ,
schedstart datetime null ,
schedfinish datetime null ,
remdur double precision null ,
crewid varchar (8) null ,
supervisor varchar (8) null ,
woeq13 datetime null ,
woeq14 decimal(15,2) null ,
wopm1 varchar (10) null ,
wopm2 varchar (10) null ,
wopm3 varchar (10) null ,
wopm4 decimal(10,2) null ,
wopm5 varchar (10) null ,
wojp1 varchar (10) null ,
wojp2 varchar (10) null ,
wojp3 varchar (10) null ,
wojp4 decimal(10,2) null ,
wojp5 datetime null ,
wol1 varchar (10) null ,
wol2 varchar (10) null ,
wol3 decimal(10,2) null ,
wol4 datetime null ,
wolablnk varchar (8) null ,
respondby datetime null ,
eqlocpriority integer null ,
calcpriority integer null ,
chargestore varchar (1) not null ,
failurecode varchar (8) null ,
wolo1 varchar (10) null ,
wolo2 varchar (10) null ,
wolo3 varchar (10) null ,
wolo4 varchar (10) null ,
wolo5 varchar (10) null ,
wolo6 decimal(10,2) null ,
wolo7 datetime null ,
wolo8 decimal(15,2) null ,
wolo9 varchar (10) null ,
wolo10 integer null ,
glaccount varchar (20) null ,
estservcost decimal(10,2) not null ,
actservcost decimal(10,2) not null ,
disabled varchar (1) null ,
estatapprlabhrs double precision not null ,
estatapprlabcost decimal(10,2) not null ,
estatapprmatcost decimal(10,2) not null ,
estatapprtoolcost decimal(10,2) not null ,
estatapprservcost decimal(10,2) not null ,
wosequence integer null ,
hasfollowupwork varchar (1) not null ,
worts1 varchar (10) null ,
worts2 varchar (10) null ,
worts3 varchar (10) null ,
worts4 datetime null ,
worts5 decimal(15,2) null ,
wfid integer null ,
wfactive varchar (1) not null ,
sourcesysid varchar (10) null ,
ownersysid varchar (10) null ,
followupfromwonum varchar (10) null ,
pmduedate datetime null ,
pmextdate datetime null ,
pmnextduedate datetime null ,
viewwoasoper varchar (1) not null ,
woassignmntqueueid varchar (8) not null ,
worklocation varchar (8) null ,
wowq1 varchar (1) null ,
wowq2 varchar (1) null ,
wowq3 varchar (1) null ,
wojp6 varchar (10) null ,
wojp7 varchar (10) null ,
wojp8 varchar (10) null ,
wojp9 decimal(10,2) null ,
wojp10 datetime null ,
wo11 decimal(10,2) null ,
wo12 decimal(10,2) null ,
wo13 datetime null ,
wo14 datetime null ,
wo15 decimal(15,2) null ,
wo16 decimal(15,2) null ,
wo17 varchar (10) null ,
wo18 varchar (10) null ,
wo19 integer null ,
wo20 varchar (1) null ,
externalrefid varchar (10) null ,
apiseq varchar (50) null ,
interid varchar (50) null ,
migchangeid varchar (50) null ,
sendersysid varchar (50) null ,
expdone varchar (25) null ,
fincntrlid varchar (8) null ,
generatedforpo varchar (8) null ,
genforpolineid integer null ,
rowstamp timestamp
)
go
insert into workorder
( wonum, parent, status, statusdate, worktype, leadcraft, description,
eqnum, location,
jpnum, faildate, changeby, changedate, estdur, estlabhrs, estmatcost,
estlabcost, esttoolcost,
pmnum, actlabhrs, actmatcost, actlabcost, acttoolcost, haschildren,
outlabcost, outmatcost, outtoolcost,
historyflag, contract, wopriority, wopm6, wopm7, targcompdate,
targstartdate, woeq1, woeq2,
woeq3, woeq4, woeq5, woeq6, woeq7, woeq8, woeq9, woeq10, woeq11,
woeq12, wo1, wo2, wo3, wo4, wo5, wo6, wo7, wo8,
wo9, wo10, ldkey, reportedby, reportdate, phone, problemcode,
calendar, interruptable,
downtime, actstart, actfinish, schedstart, schedfinish, remdur,
crewid, supervisor, woeq13,
woeq14, wopm1, wopm2, wopm3, wopm4, wopm5, wojp1, wojp2, wojp3,
wojp4, wojp5, wol1, wol2, wol3, wol4, wolablnk, respondby,
eqlocpriority,
calcpriority, chargestore, failurecode, wolo1, wolo2, wolo3, wolo4,
wolo5, wolo6,
wolo7, wolo8, wolo9, wolo10, glaccount, estservcost, actservcost,
disabled, estatapprlabhrs,
estatapprlabcost, estatapprmatcost, estatapprtoolcost,
estatapprservcost, wosequence, hasfollowupwork, worts1, worts2, worts3,
worts4, worts5, wfid, wfactive, sourcesysid, ownersysid,
followupfromwonum, pmduedate, pmextdate,
pmnextduedate, viewwoasoper, woassignmntqueueid, worklocation, wowq1,
wowq2, wowq3, wojp6, wojp7,
wojp8, wojp9, wojp10, wo11, wo12, wo13, wo14, wo15, wo16,
wo17, wo18, wo19, wo20, externalrefid, apiseq, interid, migchangeid,
sendersysid,
expdone, fincntrlid, generatedforpo, genforpolineid)
values
( '7333', '7330', 'WAPPR', '1998-09-23 22:17:00', 'CP', NULL, 'Install
turntable', NULL, 'NEEDHAM',
NULL, NULL, 'MAXIMO', '1999-03-29 19:48:00', 16, 64, 0, 1172, 34,
NULL, 0, 0, 0, 0, 'N', 0, 0, 0,
'N', NULL, 9, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, 'MAXIMO', '1998-09-23 22:17:00', NULL, 'MAINT',
NULL, NULL,
NULL, NULL, NULL, '1999-03-29 0:00:00', '1999-03-29 8:00:00', NULL,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, '6000300???', 0, 0, NULL, 0,
0, 0, 0, 0, 2, 'N', NULL, NULL, NULL,
NULL, NULL, 0, 'N', NULL, NULL, NULL, NULL, NULL,
NULL, 'N', 'PM', NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL)
go
create table pm (
pmnum varchar (8) not null ,
description varchar (50) null ,
eqnum varchar (8) null ,
firstdate datetime null ,
lastcompdate datetime null ,
laststartdate datetime null ,
usetargetdate varchar (1) not null ,
lastmeterreading decimal(15,2) not null ,
lastmeterdate datetime null ,
frequency integer not null ,
meterfrequency decimal(15,2) not null ,
pmcounter integer not null ,
priority integer not null ,
worktype varchar (5) null ,
jpnum varchar (10) null ,
jpseqinuse varchar (1) not null ,
nextdate datetime null ,
pm17 varchar (10) null ,
pm18 decimal(15,2) null ,
changedate datetime not null ,
changeby varchar (18) not null ,
pmeq1 varchar (10) null ,
pm1 varchar (8) not null ,
pm2 varchar (8) not null ,
pm3 varchar (10) null ,
pm4 datetime null ,
pm5 decimal(15,2) null ,
ldkey integer null ,
supervisor varchar (8) null ,
calendar varchar (8) null ,
crewid varchar (8) null ,
interruptable varchar (1) null ,
downtime varchar (1) null ,
pm6 varchar (10) null ,
pm7 varchar (10) null ,
pm8 varchar (10) null ,
pm9 decimal(10,2) null ,
pm10 varchar (10) null ,
pmeq2 datetime null ,
pmeq3 decimal(15,2) null ,
pmjp1 varchar (10) null ,
pmjp2 varchar (10) null ,
pmjp3 varchar (10) null ,
pmjp4 decimal(10,2) null ,
pmjp5 datetime null ,
glaccount varchar (20) null ,
location varchar (8) null ,
storeloc varchar (8) null ,
parent varchar (8) null ,
haschildren varchar (1) not null ,
wosequence integer null ,
usefrequency varchar (1) not null ,
route varchar (8) null ,
frequnit varchar (8) not null ,
meterfrequency2 decimal(15,2) not null ,
lastmeterreading2 decimal(15,2) not null ,
lastmeterdate2 datetime null ,
leadtime integer null ,
extdate datetime null ,
adjnextdue varchar (1) null ,
pm11 varchar (10) null ,
pm12 varchar (10) null ,
pm13 varchar (10) null ,
pm14 decimal(10,2) null ,
pm15 integer null ,
pm16 varchar (1) null ,
masterpm varchar (8) null ,
overridemasterupd varchar (1) not null ,
ismasterpm varchar (1) not null ,
masterpmitemnum varchar (30) null ,
applymasterpmtoeq varchar (1) not null ,
applymasterpmtoloc varchar (1) not null ,
updtimebasedfreq varchar (1) not null ,
updstartdate varchar (1) not null ,
updmeter1 varchar (1) not null ,
updmeter2 varchar (1) not null ,
updjpsequence varchar (1) not null ,
updextdate varchar (1) not null ,
updseasonaldates varchar (1) not null ,
wostatus varchar (8) not null ,
seasonstartday smallint null ,
seasonstartmonth varchar (16) null ,
seasonendday smallint null ,
seasonendmonth varchar (16) null ,
pmjp6 varchar (10) null ,
pmjp7 varchar (10) null ,
pmjp8 varchar (10) null ,
pmjp9 decimal(10,2) null ,
pmjp10 datetime null ,
rowstamp timestamp
)
go
insert into pm
( pmnum, description, eqnum, firstdate, lastcompdate, laststartdate,
usetargetdate, lastmeterreading, lastmeterdate,
frequency, meterfrequency, pmcounter, priority, worktype, jpnum,
jpseqinuse, nextdate, pm17,
pm18, changedate, changeby, pmeq1, pm1, pm2, pm3, pm4, pm5,
ldkey, supervisor, calendar, crewid, interruptable, downtime, pm6,
pm7, pm8,
pm9, pm10, pmeq2, pmeq3, pmjp1, pmjp2, pmjp3, pmjp4, pmjp5,
glaccount, location, storeloc, parent, haschildren, wosequence,
usefrequency, route, frequnit,
meterfrequency2, lastmeterreading2, lastmeterdate2, leadtime, extdate,
adjnextdue, pm11, pm12, pm13,
pm14, pm15, pm16, masterpm, overridemasterupd, ismasterpm,
masterpmitemnum, applymasterpmtoeq, applymasterpmtoloc,
updtimebasedfreq, updstartdate, updmeter1, updmeter2, updjpsequence,
updextdate, updseasonaldates, wostatus, seasonstartday,
seasonstartmonth, seasonendday, seasonendmonth, pmjp6, pmjp7, pmjp8,
pmjp9, pmjp10)
values
( 'PM-CONV2', 'Conveyor Overhaul- Conveyor #2', '12700', '1999-03-03
00:00:00', '1996-11-13 00:00:00', '1999-03-30 00:00:00', 'Y', 0, NULL,
90, 0, 1, 8, 'PM', 'JP1314A', 'Y', '1999-06-28 00:00:00', '332',
NULL, '1999-03-30 18:40:00', 'MAXIMO', NULL, 'MAINT', 'PM', NULL,
NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'Y', NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, 'CENTRAL', NULL, 'N', NULL, 'N', NULL, 'DAYS',
0, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, 'N', 'N', NULL, 'Y', 'Y',
'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'WSCH', NULL,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
go
Hugo Kornelis wrote:
On 20 Jan 2005 09:07:57 -0800, Martin wrote:
Hello Guys,
Thank you for all your feedback!.
Below is the trigger that I'm using and Hugo is right there are many
PMs on WOs so it updates all the WO that matches the PMNUM.. (sniup trigger code)
Hi Martin,
The code you posted is even worse: it will update ALL rows currently
in the workorder table. All these rows will have their
woassignmntqueueid and their problemcode set to PM2 and PM1 from a pm row that matches one
of the inserted rows - and if multiple rows are inserted, the trigger will
just choose one, semi-randomly.
I'm quite sure that this is not what you want - but I have no idea
what you do want.
Any way, here is now the situation, these fields, woassignmntqueueid
and problemcode are required (NOT NULL ALLOWED) on the workorder
table;so, this trigger never executes.
This conclusion is wrong. Whether these rows allow NULLS or not has
nothing to do with the firing of this trigger. As soon as an INSERT
statement is run against the workorder table, this trigger *WILL*
run, and it *WILL* attempt to update *all* rows in workorder.
Of course, if the chosen value for either woassignmntqueueid or
problemcode happens to be NULL, the update will fail, causing an
error in the trigger and a rollback of the entire transaction (including the
insert statement that caused the trigger to fire). But the trigger DOES
execute!
What do I need to do, to update only
the current workorder passing pm.pm2 and pm.pm1 to
woassignmntqueueidand problemcode.
I'mm sorry, but your narrative is not sufficient to explain your
exact requirements. I suggest you post
* The structure of all related tables (as CREATE TABLE statements,
including datatypes, constraints and properties; irrelevant columns
may be omitted, especially if there are lots of them),
* Some illustrative sample data (as INSERT statements, so that I can
use cut and paste to run the code in Query Analyzer and recreate your
sample data on my test database),
* The required output, and
* A concise description of the business problem you're trying to
solve.
Check out this site as well: http://www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)