Insert Trigger Help

[Martin]

Hello,

I'm new with triggers and I can not find any good example on how to
do the following:

I have two tables WO and PM with the following fields:

WO.WONUM, VARCHAR(10)
WO.PMNUM, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
WO.LABORGROUP, VARCHAR(8)

PM.PMNUM, VARCHAR(10)
PM.PROBLEMCODE, VARCHAR(8)
PM.LABORGROUP, VARCHAR(8)

When creating a new record on WO I need to create an INSERT TRIGGER
that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM

PM.PROBLEMCODE to WO. PROBLEMCODE and
PM.LABORGROUP to WO. LABORGROUP

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.

Thanks!

Martin

[Simon Hayes]

"Martin" <ma***********@ wsidc.com> wrote in message
news:11******** *************@c 13g2000cwb.goog legroups.com...

Hello,

I'm new with triggers and I can not find any good example on how to
do the following:

I have two tables WO and PM with the following fields:

WO.WONUM, VARCHAR(10)
WO.PMNUM, VARCHAR(10)
WO.PROBLEMCODE, VARCHAR(8)
WO.LABORGROUP, VARCHAR(8)

PM.PMNUM, VARCHAR(10)
PM.PROBLEMCODE, VARCHAR(8)
PM.LABORGROUP, VARCHAR(8)

When creating a new record on WO I need to create an INSERT TRIGGER
that will pass the data below from PM to WO when WO.PMNUM = PM.PMNUM

PM.PROBLEMCODE to WO. PROBLEMCODE and
PM.LABORGROUP to WO. LABORGROUP

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.

Thanks!

Martin

I don't really understand your description - are you saying that when you
insert a row into WO you want to update PROBLEMCODE and LABORGROUP with
corresponding values from the PM table, joined on the PMNUM column?

In future, please post table structure as DDL, ie. CREATE TABLE statements,
so it's clear what your keys and constraints are, along with INSERT
statements for sample data - this is much clearer than a description.

See below for sample code - it may be incorrect, but hopefully it will get
you started, at least.

Simon

create trigger dbo.ITR_WO
on dbo.WO after insert
as
begin
if @@rowcount = 0
return

update
dbo.WO
set
LABORGROUP=p.LA BORGROUP,
PROBLEMCODE=p.P ROBLEMCODE
from
dbo.PM p
join dbo.WO w
on i.PMNUM = WO.PMNUM
end

[SQLDBA]

You need to create an Insert Trigger in the WO table to do an update
joining the PM table when the keys are the same. Let me know if you
need the syntex..!

[Hugo Kornelis]

On 4 Jan 2005 10:04:18 -0800, Martin wrote:

(snip)

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.

Hi Martin,

Simon already gave you some code that might do what you want (but beware
of unexpected results if one row in PM is matched by more than one row in
WO), but I'd like to question the reason for what you want to do.

The table names WO and PM don't reveal anything about your business, of
course, so I might be wrong - but from the looks of it, your design is
violating third normal form. Are you sure that you wouldn't be better off
removing the problemcode and laborgroup from the WO table, and joining the
PM table in when you need to report these properties for a given WOnum?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

Simon Hayes (sq*@hayes.ch) writes:

create trigger dbo.ITR_WO
on dbo.WO after insert
as
begin
if @@rowcount = 0
return

update
dbo.WO
set
LABORGROUP=p.LA BORGROUP,
PROBLEMCODE=p.P ROBLEMCODE
from
dbo.PM p
join dbo.WO w
on i.PMNUM = WO.PMNUM
end

The line

join dbo.WO w

ought to be

join inserted w

Simon knows this as well, but for Martin this call for an explanation.
"inserted" is a virtual table that holds the rows inserted by the
INSERT statement. Note that the trigger fires once per statement, and
the virttual table, thus can have many rows.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Simon Hayes]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** ************@12 7.0.0.1...

Simon Hayes (sq*@hayes.ch) writes:

create trigger dbo.ITR_WO
on dbo.WO after insert
as
begin
if @@rowcount = 0
return

update
dbo.WO
set
LABORGROUP=p.LA BORGROUP,
PROBLEMCODE=p.P ROBLEMCODE
from
dbo.PM p
join dbo.WO w
on i.PMNUM = WO.PMNUM
end

The line

join dbo.WO w

ought to be

join inserted w

Simon knows this as well, but for Martin this call for an explanation.
"inserted" is a virtual table that holds the rows inserted by the
INSERT statement. Note that the trigger fires once per statement, and
the virttual table, thus can have many rows.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Oops, my mistake - thanks for the correction, Erland.

Simon

[Martin]

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

CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @@rowcount = 0
RETURN
UPDATE [workorder]
SET
woassignmntqueu eid = P.PM2,
problemcode = P.PM1
FROM
[pm]AS P join inserted AS I
on P.PMNUM = I.PMNUM
END
Any way, here is now the situation, these fields, woassignmntqueu eid
and problemcode are required (NOT NULL ALLOWED) on the workorder table;
so, this trigger never executes. What do I need to do, to update only
the current workorder passing pm.pm2 and pm.pm1 to woassignmntqueu eid
and problemcode.

Any help will be appreciated.

Thanks!

Martin
Hugo Kornelis wrote:

On 4 Jan 2005 10:04:18 -0800, Martin wrote:

(snip)

Could anybody please show me how to do this or point me to the right
direction, any help will be greatly appreciated.
Hi Martin,

Simon already gave you some code that might do what you want (but

beware of unexpected results if one row in PM is matched by more than one row in WO), but I'd like to question the reason for what you want to do.

The table names WO and PM don't reveal anything about your business, of course, so I might be wrong - but from the looks of it, your design is violating third normal form. Are you sure that you wouldn't be better off removing the problemcode and laborgroup from the WO table, and joining the PM table in when you need to report these properties for a given WOnum?
Best, Hugo

[Hugo Kornelis]

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 woassignmntqueu eid 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, woassignmntqueu eid
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 woassignmntqueu eid 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 woassignmntqueu eid
and 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)

[Martin]

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 woassignmntqueu eid 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.probl emcode = pm.pm1
Workorder.woass ignmntqueueid = pm.pm2

PM Table
Pmnun Description Pm1 Pm2
Hvac001 Monthly A/C Unit PM HVAC MAINT

Workorder Table
Wonum Description Pmnum problemcode woassignmntqueu eid
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 ,
estatapprlabcos t decimal(10,2) not null ,
estatapprmatcos t decimal(10,2) not null ,
estatapprtoolco st decimal(10,2) not null ,
estatapprservco st 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 ,
followupfromwon um varchar (10) null ,
pmduedate datetime null ,
pmextdate datetime null ,
pmnextduedate datetime null ,
viewwoasoper varchar (1) not null ,
woassignmntqueu eid 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 ,
estatapprlabcos t, estatapprmatcos t, estatapprtoolco st,
estatapprservco st, wosequence, hasfollowupwork , worts1, worts2, worts3,
worts4, worts5, wfid, wfactive, sourcesysid, ownersysid,
followupfromwon um, pmduedate, pmextdate,
pmnextduedate, viewwoasoper, woassignmntqueu eid, 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 ,
lastmeterreadin g 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 ,
lastmeterreadin g2 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 ,
overridemasteru pd varchar (1) not null ,
ismasterpm varchar (1) not null ,
masterpmitemnum varchar (30) null ,
applymasterpmto eq varchar (1) not null ,
applymasterpmto loc varchar (1) not null ,
updtimebasedfre q 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 ,
updseasonaldate s varchar (1) not null ,
wostatus varchar (8) not null ,
seasonstartday smallint null ,
seasonstartmont h 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, lastmeterreadin g, 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 , lastmeterreadin g2, lastmeterdate2, leadtime, extdate,
adjnextdue, pm11, pm12, pm13,
pm14, pm15, pm16, masterpm, overridemasteru pd, ismasterpm,
masterpmitemnum , applymasterpmto eq, applymasterpmto loc,
updtimebasedfre q, updstartdate, updmeter1, updmeter2, updjpsequence,
updextdate, updseasonaldate s, wostatus, seasonstartday,
seasonstartmont h, 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 woassignmntqueu eid 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, woassignmntqueu eid
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 woassignmntqueu eid 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 woassignmntqueu eidand 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)

[Hugo Kornelis]

On 24 Jan 2005 05:31:04 -0800, Martin wrote:

(snip)

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
Hi Martin,

Based on what I read, it appears that you're fighting the symptoms instead
of addressing the cause. It seems to me that the problem is that the code
that generates work orders from PM entries fails to provide the
problemcode and assignmentqueue , even though they ARE available in the PM
table. Could you post the code that generates new work orders from the
rows in the PM table? I guess that THAT is where the real key to solving
your problem lies.

Workorder.prob lemcode = pm.pm1
Workorder.woas signmntqueueid = pm.pm2

PM Table
Pmnun Description Pm1 Pm2
Hvac001 Monthly A/C Unit PM HVAC MAINT

Workorder Table
Wonum Description Pmnum problemcode woassignmntqueu eid
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.

In the mean time, the above contains the info I need to help you with the
trigger. Since there is a foreign key from Workorder to PM, it's possible
to find the one and only PM row that a workorder should be coupled to and
take PM1 and PM2 from that row.

The code below won't update the assignment queue or problemcode values if
no Pmnum is specified in the new row. I assume that Omnum is only present
if a workorder is generated for preventive maintenance and that assignment
queue and workorder should not be changed for other work orders.

If you need the ability to override the problemcode and assignmentqueue
from the PM tables, you need to make two changes:
* change your frontend code so that overriding values for problemcode and
assignment queue can be included in the INSERT statement
* change the SET clauses to (using problemcode as an example)
SET problemcode = COALESCE (w.problemcode, P.PM1)
this will ensure that the value entered is retained, bot if no value is
entered (the value is NULL), it will be replaced by the PM1 value.
CREATE TRIGGER GENERATE_PM_WO ON [workorder]
FOR INSERT
AS
BEGIN
IF @@rowcount = 0
RETURN
UPDATE w
SET woassignmntqueu eid = P.PM2,
problemcode = P.PM1
FROM workorder AS w
INNER JOIN pm AS p
ON p.Pmnum = w.Pmnum
INNER JOIN inserted AS i
ON i.Wonum = w.Wonum
-- Note - the inner join to inserted ensures only new rows are affected.
-- This could just as well have been written as an EXISTS or IN subquery.
END

(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)