Please help me to write trigger for update on single column

Question

Post reply

Please help me to write trigger for update on single column

Bals.Prasanna

SSC Enthusiast

Points: 124
More actions
May 9, 2014 at 12:01 am

#288554

Hi,
I have table "project" as below
ProjNum E0E1E2
1234.0 90100 200
1234.1 90100 200
12345.0 3020 100
12345.1 1010 50
12345.2 2010 50
Here the proj num with X.0 will be considered as master and X.1,X.2,X.3... will be considered as sub project for X. I need to write a trigger, such that when an insert/update happens in columns(E0,E1,E2) of sub project, then i should sum the values of this and update it to(E0,E1,E2) of master project, if delete then it should substract that value from the master.
for ex: when 1234.2 is added with (E0,E1,E2) values (10,10,10) then table values should be look like
ProjNum E0E1E2
1234.0 100 110 210
1234.1 90100 200
1234.2 1010 10
when 12345.2 is deleted then
ProjNum E0E1E2
12345.0 1010 50
12345.1 1010 50
DDL of this table
CREATE TABLE "PROJECT"
(
"PRJ_NMBR" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
"PRJ_NAME" VARCHAR2(4000 BYTE) NOT NULL ENABLE,
"E0" NUMBER(20,0),
"E1" NUMBER(20,0),
"E2" NUMBER(20,0),
"EXCEPTION_APPROVED_BY" VARCHAR2(4000 BYTE),
"EXCEPTION_APPROVED_DATE" DATE
)
Please help me, thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

your schema tells that you are working on Oracle, it is a SQL SERVER related site.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Despite the fact that you are working with Oracle I would highly recommend you read up on normalization. The structures you presented need some serious help with normalization. You have a column that holds the value for multiple rows. This is painful to work with as you discovering. If you had this properly normalized there would be no need for such a trigger.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

Sean Lange (5/9/2014)
Despite the fact that you are working with Oracle I would highly recommend you read up on normalization. The structures you presented need some serious help with normalization. You have a column that holds the value for multiple rows. This is painful to work with as you discovering. If you had this properly normalized there would be no need for such a trigger.

BWAAA-HAAA!!!. Ironically, I say the same thing about HierarchyID and XML! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

@Bals.Prasanna,

Howdy.

I'm not sure that I remember how to write Oracle triggers (they're single row based and then powered by a FOR EACH ROW for sets of rows) but I can show you how to do this using T-SQL triggers. Perhaps you could get something from that. I'm on my way to work. If you would take the time to build some readily consumable test data (see the first link under "Helpful Links" in my signature line below), that would save me some time and I could take a crack at it after work tonight.

The only question that I have is, will this be limited to just the two levels your data portrays or can there be more than two levels? If so, what will the structure of that data look like?

Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bals.Prasanna SSC Enthusiast Points: 124 More actions · Answer 5

Thanks Jeff.

Here is the sample data

--===== Create the test table with

CREATE TABLE PROJECT

(

"PRJ_NMBR" VARCHAR2(4000 BYTE) NOT NULL ENABLE,

"PRJ_NAME" VARCHAR2(4000 BYTE) NOT NULL ENABLE,

"E0" INT,

"E1" INT,

"E2" INT,

"EXCEPTION_APPROVED_BY" VARCHAR2(4000 BYTE),

"EXCEPTION_APPROVED_DATE" DATETIME

)

--===== Insert the test data into the test table

INSERT INTO PROJECT

(PRJ_NMBR, PRJ_NAME, E0, E1, E2,EXCEPTION_APPROVED_BY,EXCEPTION_APPROVED_DATE)

SELECT '1234.0','TEST',90,100,200,null,null UNION ALL

SELECT '1234.1','TEST',90,100,200,null,null UNION ALL

SELECT '12345.0','TEST1',30,20,100,null,null UNION ALL

SELECT '12345.0','TEST1',10,10,50,null,null UNION ALL

SELECT '12345.0','TEST1',20,10,50,null,null UNION ALL