473,718 Members | 2,068 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Insert and Update Trigger on same Table

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.F55MRKT1 1
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.f55MRKT1 1)

END

TRIGGER #2:

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

UPDATE CRPDTA.F55MRKT1 1
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.F55MRKT1 1
on(ins.mhan8=md an8)

END

For instance if I do the following insert:

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

then

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

This is what I expect in both tables:

[CRPDTA.F55MRKT1 19] (2 Records)

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

[CRPDTA.F55MRKT1 1] (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.

Jul 23 '05 #1
1 15422
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.F55MRKT1 19
FOR INSERT
AS
BEGIN
-- Update F55MRKT11 for new F55MRKT119 rows with existing MHAN8 value
UPDATE CRPDTA.F55MRKT1 1
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.F55MRKT1 1
(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.F55MRKT1 1 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)
Jul 23 '05 #2

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;
9
3456
by: Martin | last post by:
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)
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...
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...
1
9041
by: db2admin | last post by:
Hello, I lack knowledge about triggers. I have created trigger on table A Table A ------------------------------------------------- ID INTEGER CASEID INTEGER CEID INTEGER AS_CD CHAR(2)
1
1887
by: degno84 | last post by:
hi.. i have a problem with this simple trigger! I wont to insert a row in the same table where I update a value. The trigger is that: CREATE OR REPLACE TRIGGER LOG_SPOSTAMENTI_NEW_DEST AFTER update of data_fine, luogo_spostamento on LOG_SPOSTAMENTI for each row
0
2288
by: magnolia | last post by:
i created a trigger that will record the changes made to a table .everything works fine except the insert query.whenerever i try to insert a record it fires insert and update triger at the same time which means i hav 2 record for every insert operation. any help appreciated. thank u herez teh code i tried. ALTER TRIGGER trg_ContactAddress_Audit ON Address FOR DELETE, INSERT, UPDATE AS
1
2279
by: veasnamuch | last post by:
I have a problem while I create a trigger to my table. My objective is getting any change made to my table and record it in to another table . My have thousands records before I add new trigger to this table, everything work fine means, when I manual insert data into table the last row of inserting is what I am doing but after I add trigger on inserting to this table and I do next manual insert data into the last row in Enterprise Manager, I...
0
9358
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...
1
9121
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,...
0
7991
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6654
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
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4481
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...
1
3182
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
2
2559
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2123
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.