Insert and Update Trigger on same Table

[shottarum]

I currently have 2 tables as follows:

CREATE TABLE [CRPDTA].[F55MRKT119]
(
mhan8 int,
mhac02 varchar(5),
mhmot varchar(5),
mhupmj int
)

GO

CREATE TABLE [CRPDTA].[F55MRKT11]
(
mdan8 int,
mdac02 varchar(5),
mdmot varchar(5),
mdmail int,
mdmag int,
mdupmj int
)

What I would like to do is place a trigger on F55MRKT119 which will
insert records to the F55MRKT11 if they do not exist in that table
based on the [mdan8] field. If the record does exist I would like
Update the corresponding record and increment either the [MDMAIL] or
the [MDMAG] based on the inserted [MHMOT]. What I have so far is as
follows:

TRIGGER #1:

CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]
FOR INSERT
AS
BEGIN

INSERT INTO CRPDTA.F55MRKT11
select INS.MHAN8, INS.MHAC02, INS.MHMOT,
case when INS.MHMOT='MAG' then 0 ELSE 1 end,
case when INS.MHMOT='MAG' then 1 ELSE 0 end,
'0' from INSERTED INS
WHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)

END

TRIGGER #2:

CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]
FOR Update
AS
BEGIN

UPDATE CRPDTA.F55MRKT11
SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAIL
when INS.MHMOT<>'MAG' then 1+MDMAIL end,
MDMAG= case when INS.MHMOT='MAG' then 1+MDMAG
when INS.MHMOT<>'MAG' then 0+MDMAG end
from INSERTED INS JOIN CRPDTA.F55MRKT11
on(ins.mhan8=mdan8)

END

For instance if I do the following insert:

INSERT INTO CRPDTA.F55MRKT119
VALUES('212131','VK4','AL4','0')

then

INSERT INTO CRPDTA.F55MRKT119
VALUES('212131','VK4','MAG','0')

This is what I expect in both tables:

[CRPDTA.F55MRKT119] (2 Records)

MHAN8 MHAC02 MHMOT MHUPMJ
------ ------ ----- ------
212131 VK4 AL4 0
212131 VK4 MAG 0

[CRPDTA.F55MRKT11] (1 Record)

MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ
----- ------ ----- ------ ----- ------
212131 VK4 AL4 1 1 0

The insert part works fine in that it iserts in both tables with the
correct values. However it seems as if the Update protion is failing
for some reason. WHat I have tried so far is setting the trigger order
for the update to run first and vice-versa, but still no luck. Any
help would be appreciated.

[Hugo Kornelis]

On 31 Dec 2004 08:19:20 -0800, shottarum wrote:

(snip)

The insert part works fine in that it iserts in both tables with the
correct values. However it seems as if the Update protion is failing
for some reason. WHat I have tried so far is setting the trigger order
for the update to run first and vice-versa, but still no luck. Any
help would be appreciated.

Hi shottarum,

Unless I misunderstand you, you have a misconception about the purpose of
INSERT vs UPDATE triggers. The sole difference between these two is what
fires them, not what they do.

Your post doesn't include the keys of your tables. Your table and column
names are completely non-descriptive as well. This combination makes it
hard to address your post. But I'll give it a try anyway.

If I don't misunderstand you, you want the following:
* For each row inserted in F55MRKT119 with a MHAN8 value that doesn't yet
exist in F55MRKT11, make a copy of that row in F55MRKT11, with the values
for mdmail and mdmag calculated from the new F55MRKT119 row.
* For each row inserted in F55MRKT119 with a MHAN8 value that does already
exist in F55MRKT11, increase mdmail and mdmag, based on the new F55MRKT119
row.

Your post doesn't mention anything about changing F55MRKT11 when rows in
F55MRKT119 are updated or deleted. This means you'll only need an INSERT
trigger, to make all desired changed to F55MRKT11 when row(s) are inserted
in F55MRKT119. You'll need to add the update statement to the insert
statement. It has to go before the insert statement, else the inserted
rows will be updated again in the same trigger execution!

I already said that knowing nothing about your keys makes answering your
question harder. I'll have to make some guesses here. The fact that you
foresee inserts in F55MRKT119 with a MHAN8 that exists in F55MRKT11 (and
is probably based on another row, inserted previously in F55MRKT119)
suggests that MHAN8 is not the primary key for F55MRKT119, but it probably
is for F55MRKT11. This means that both your insert and your update need
some change.

Your insert is flawed because two or more rows with the same value for
MHAN8 might be inserted at once. If that value of MHAN8 is not yet present
in F55MRKT11, your insert statement would insert both new rows in
F55MRKT11, violating the primary key. To prevent this, you'll have to
change the INSERT statement to include aggregation and group by MHAN8. I
have no idea which value you'd like to pick for MDAC02 and MDMOT though.

Your update is flawed because the non-standard UPDATE FROM syntax you used
can have nasty side effects if one row from the table to be updated can be
matched against more than one row from the joined table(s). In your case,
that will happen if two or more rows are inserted with the same MHAN8 that
is already present in F55MRKT11.

I think your trigger has to look something like the below:

CREATE TRIGGER trgIns_Summary ON CRPDTA.F55MRKT119
FOR INSERT
AS
BEGIN
-- Update F55MRKT11 for new F55MRKT119 rows with existing MHAN8 value
UPDATE CRPDTA.F55MRKT11
SET MDMAIL = MDMAIL +
(SELECT COUNT(*)
FROM inserted AS ins
WHERE ins.MHAN8 = F55MRKT11.MDAN8
AND ins.MHMOT <> 'MAG')
,MDMAG = MDMAG +
(SELECT COUNT(*)
FROM inserted AS ins
WHERE ins.MHAN8 = F55MRKT11.MDAN8
AND ins.MHMOT = 'MAG')
WHERE EXISTS (SELECT *
FROM inserted AS ins
WHERE ins.MHAN8 = F55MRKT11.MDAN8)
-- Add rows to F55MRKT11 for new F55MRKT119 rows with new MHAN8 value
INSERT INTO CRPDTA.F55MRKT11
(MDAN8, MDAC02, MDMOT,
MDMAIL, MDMAG, MDUPMJ) -- Always include column list!
SELECT ins.MHAN8, MAX(ins.MHAC02), MAX(ins.MHMOT),
SUM(CASE WHEN ins.MHMOT='MAG' THEN 0 ELSE 1 END),
SUM(CASE WHEN ins.MHMOT='MAG' THEN 1 ELSE 0 END),
'0'
FROM inserted AS ins
WHERE NOT EXISTS (SELECT *
FROM CRDPTA.F55MRKT11 AS f
WHERE f.MDAN8 = ins.MHAN8)
GROUP BY ins.MHAN8
END

The above is untested. Please consult http://www.aspfaq.com/5006 before
posting more questions, as it saves everyone involved (including you!) a
lot of time!

Another advise: using all caps for table and column names generally makes
code harder to read. I'm tempted to say that using meaningless table and
column names makes it hard as well, but I guess that you already know
that, but are forced to because of company policy. Nevertheless, changing
then to more human-understandable names before posting to usenet might be
well worth considering.

Best, Hugo
--

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