473,856 Members | 1,568 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Re: How to avoid 2nd trigger

What about using delete/insert instead of update?

Lennart wrote:
>I am currently developing trigger based screen edit fro several 100 screens
in COBOL/CICS
[quoted text clipped - 81 lines]
>I don't have MERGE STATEMENT, and I don't have INSTEAD OF TRIGGERS.
Any Ideas?

I don't think you can avoid that since triggers are fired either after
update or insert. Merge wont do you any good and instead of triggers
only works for views.

Is there some specific reason that you don't want to use check
constraints and foreign keys for this purpose?

/Lennart
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 14 '08 #1
8 1543
On Jul 14, 1:05 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
What about using delete/insert instead of update?
I don't follow, are you going to forbid update operations on your
tables? If that is the case already have the insert trigger, so you
shouldn't have to do anything more.

I still don't understand why you are implementing this validation via
triggers instead of declarative constraints.

/Lennart

[...]
Jul 14 '08 #2
How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?

Lennart wrote:
>What about using delete/insert instead of update?

I don't follow, are you going to forbid update operations on your
tables? If that is the case already have the insert trigger, so you
shouldn't have to do anything more.

I still don't understand why you are implementing this validation via
triggers instead of declarative constraints.

/Lennart

[...]
--
Message posted via http://www.dbmonster.com

Jul 14 '08 #3
On Jul 14, 3:28 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?
ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( BIRTHDATE < HIREDATE )

/Lennart

[...]

Jul 14 '08 #4
Thank you Lennart.
I like this approach.
One more question. What if you a manager or superviser then commissions
always should be zero and education level more then 10.

Lennart wrote:
>How can i use declarative constraints for relarional edits?
For exam[le date of bitrh should always less than date of hire?

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( BIRTHDATE < HIREDATE )

/Lennart

[...]
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 14 '08 #5
On Jul 14, 5:02 pm, "lenygold via DBMonster.com" <u41482@uwewrot e:
Thank you Lennart.
I like this approach.
One more question. What if you a manager or superviser then commissions
always should be zero and education level more then 10.
In general, DB2 does not support sub selects in check constraints, but
as long as the rule regards a relationship between columns in the same
row, it is a good candidate for a check constraint (it is not possible
at all times though). If I understand your table this case should be
something like (I assume supervisor and manager belongs to the JOB
domain) :

ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )

If you are a manager or supervisor, [ NOT ( JOB IN ('manager',
'superviser')) ] evaluates to FALSE, hence [ (commissions = 0 AND
EDLEVEL 10 ) ] must evaluate to TRUE for the constraint to evaluate
to TRUE.

A number of the attributes in the table are nullable and you have to
be extra careful with these (but that holds whether you put them in a
trigger or in a constraint). For example, what does it mean if COMM is
null, is that ok for a supervisor?
/Lennart
Jul 14 '08 #6
On Jul 14, 6:32 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
[...]
ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )
Perhaps I should have elaborated a bit more about this.

A =B is the same as NOT A OR B. In your case:

JOB IN ('manager', 'superviser') =( COMM = 0 AND EDLEVEL 10 )

can be rewritten as

(NOT JOB IN ('manager', 'superviser')) OR (COMM = 0 AND EDLEVEL 10 )

/Lennart

Jul 14 '08 #7
Thank's again Lennart.
I will test this constrain.
Lennart wrote:
>[...]
>ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
CHECK ( NOT ( JOB IN ('manager', 'superviser')) OR ( COMM = 0 AND
EDLEVEL 10 ) )

Perhaps I should have elaborated a bit more about this.

A =B is the same as NOT A OR B. In your case:

JOB IN ('manager', 'superviser') =( COMM = 0 AND EDLEVEL 10 )

can be rewritten as

(NOT JOB IN ('manager', 'superviser')) OR (COMM = 0 AND EDLEVEL 10 )

/Lennart
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 14 '08 #8
Hi again Lennart.
I found this example on our board on 12-22-2003 14:30
is this doable in DB2 OS/390 V8.2

The detail

create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMET HOD char(35) not null,
USEFULLIFE smallint not null
);

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod ,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );

create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost ResidualValue)
);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);

And now the one with the multi-table check constraint

create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDAT E date not null,
DEPRECIABLEAMOU NT numeric(8,2) not null,
check (NOT EXISTS (select b.assetID
from depreciation as a, asset as b, assetClass as c
where a.AssetID = b.AssetID and
b.assetClass = c.AssetClass and
a.depreciationD ate dateadd(year, c.usefulLife,
b.dateOfPurchas e) ))
);

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2004-02-28', 600.00);

Allowed but subsequent inserts fail????? It seems as if my dbms
evaluates the check on the existing table before inserting the new
record???

3 WEEKS LATER:

01-07-2004 01:31
OK i think i now understand how to relate the record/fields to be inserted
with existing data in the db.

So here goes again

create table ASSETCLASS
(
ASSETCLASS char(2) not null,
DESCRIPTION char(50),
DEPRECIATIONMET HOD char(35) not null,
USEFULLIFE smallint not null
);

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod ,
UsefulLife )
VALUES ('CS', 'Computer Software', 'Straight Line', 2 );

INSERT
INTO AssetClass (AssetClass, Description,Dep reciationMethod , UsefulLife )
VALUES ('MV', 'Motor Vehicles', 'Sum of Digits', 5 );

create table ASSET
(
ASSETID char(6) not null,
ASSETCLASS char(2) not null,
DESCRIPTION char(50) not null,
COST numeric(8,2) not null,
DATEOFPURCHASE date not null,
RESIDUALVALUE numeric(8,2) not null,
check (Cost ResidualValue)
);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('CS0003', 'CS', 'MS Office 2000', 2400.00, '2001-11-01',
0.00);

INSERT
INTO Asset (AssetID, AssetClass, Description, Cost, DateOfPurchase,
ResidualValue)
VALUES ('MV0005', 'MV', 'LDV HGG702 GP', 30000.00, '1998-06-01', 7500.00);

And now the one with the multi-table check constraint

create table DEPRECIATION
(
ASSETID char(6) not null,
DEPRECIATIONDAT E date not null,
DEPRECIABLEAMOU NT numeric(8,2) not null,
check (NOT EXISTS (select a.assetID
from asset as a, assetClass as b
where AssetID = a.AssetID and
a.assetClass = b.AssetClass and
depreciationDat e >= dateadd(year, b.usefulLife, a.dateOfPurchas e) ))
);

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2003-05-31', 600.00);

works but

INSERT
INTO Depreciation (AssetId, DepreciationDat e, DepreciableAmou nt)
VALUES ('CS0003', '2003-06-07', 600.00);

fails - the usefullLife of MV are used and not that of CS ????

select a.assetID
from asset as a, assetClass as b
where 'CS0003' = a.AssetID and
a.assetClass = b.AssetClass and
<put date here= dateadd(year, b.usefulLife, a.dateOfPurchas e)

only returns assetID for dates >= 2003-11-01 which is what one would expect,
however when used as search condition in check constraint things go wrong.


lenygold wrote:
>Thank's again Lennart.
I will test this constrain.
>>[...]
>>ALTER TABLE EMP_SCREEN_EDIT ADD CONSTRAINT <NAME>
[quoted text clipped - 12 lines]
>>
/Lennart
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200807/1

Jul 14 '08 #9

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

Similar topics

22
10679
by: T.S.Negi | last post by:
Hi All, I want to avoid using cursors and loops in stored procedures. Please suggest alternate solutions with example (if possible). Any suggestion in these regards will be appreciated. Thanks in advance, T.S.Negi
2
2687
by: Danny Wang | last post by:
HI, I have a dialog (window) with a drop-down list and an OK button. When user clicks the drop-down list, the app need to do some calculation and validation, so I put an Text_on_change event on the drop-down. When click OK the dialog should be closed; however, if the user already clicked the drop-down, he needs to click OK button twice to close the window. After a little research I found out the once the focus is moved out the drop-down...
9
9317
by: Ots | last post by:
I'm using SQL 2000, which is integrated with a VB.NET 2003 app. I have an Audit trigger that logs changes to tables. I want to apply this trigger to many different tables. It's the same trigger, with the exception of the table name. I could manually change the table name in the trigger and create it, over and over, but I'd like to automate this - by iterating through the collection of tables and passing the tablename to something that...
8
4601
by: nguillot | last post by:
Hello. If I have the following classes: class B {}; typedef B tB; if A is: class A
0
10696
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
10782
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
10384
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7094
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
5761
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
5958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4575
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
4174
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3201
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.