473,734 Members | 2,576 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert Trigger Help

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

Jul 23 '05 #1
9 3457

"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
Jul 23 '05 #2
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..!

Jul 23 '05 #3
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)
Jul 23 '05 #4
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
Jul 23 '05 #5

"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
Jul 23 '05 #6
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


Jul 23 '05 #7
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)
Jul 23 '05 #8
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)


Jul 23 '05 #9
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)
Jul 23 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
22198
by: DTB | last post by:
I am having trouble creating an INSTEAD OF trigger in SQL Server to replicate a BEFORE UPDATE trigger from ORACLE. Here is a sample of the ORACLE BEFORE UPDATE trigger: CREATE TRIGGER myTRIGGER ON MYTABLE begin :new.DT := SYSDATE; if :new.NM is NULL then :new.NM := USER; end if; end myTRIGGER;
1
15425
by: shottarum | last post by:
I currently have 2 tables as follows: CREATE TABLE . ( mhan8 int, mhac02 varchar(5), mhmot varchar(5), mhupmj int )
3
7280
by: takilroy | last post by:
Hi, Does anyone know of a simple way to do this? I want to create an insert trigger for a table and if the record already exists based on some criteria, I want to update the table with the values that are passed in via the insert trigger without having to use all the 'set' statements for each field (so if we add fields in the future I won't have to update the trigger). In other words, I want the trigger code to look something like...
1
6301
by: Derek Erb | last post by:
SQL Server 2000 : I have a series of tables which all have the same structure. When any of these tables are modified I need to syncrhonise all of those modifications with one other table wich is a sort of merge of the individual tables with one extra column. For most of these tables this is not a problem. The problem arrives when one of the tables has an ntext column which obviously can not be used in an update or insert trigger. ...
2
6433
by: 73blazer | last post by:
Perhaps my thinking is wrong but this is what I have: 1 table (Tab1) with 1 attribute (Attr1) Attr1 char(16) for bit data ----------------------------------------------- create trigger check no cascade before insert on Tab1 referencing new as N
3
3729
by: teddysnips | last post by:
I need a trigger (well, I don't *need* one, but it would be optimal!) but I can't get it to work because it references ntext fields. Is there any alternative? I could write it in laborious code in the application, but I'd rather not! DDL for table and trigger below. TIA
1
6209
by: abhi81 | last post by:
Hello All, I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp. Insert and Update trigger work fine when i have only one of them defined. However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and...
2
3752
by: lenygold via DBMonster.com | last post by:
Hi Everebody: I have a table: CREATE TABLE CROSS_REFERENCE (ROW# INTEGER NOT NULL ,KEY_WORD CHAR(16) NOT NULL ,QUERY_DESCR VARCHAR(330) NOT NULL ,PRIMARY KEY (ROW#,KEY_WORD)); It is a cross reference table to my CATALOG Table based on key words.
7
5791
by: anu b | last post by:
Hi I need to use Clr trigger for insert command My code is as below I am using SQL server 2005 and VS 2008.... but after running this code i didnt get the result as i expexted it shows the result as no row is effected ...Please help me guys using System;
0
8946
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
9452
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9237
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6737
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4551
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4813
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3262
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2180
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.