1

I have two tables ProjectsTable and ExpenseTable given following and a trigger ExpenseSum which update ProjectsTable when a value is inserted in ExpenseTable table. Now, I want to modify Trigger so that it can check ExpenseTotel whether it exceeds TotalCost or not if it exceeds TotalCost then give a simple message and prevent to insert in ExpenseTable otherwise insert in ExpenseTable and update in ProjectsTable. Can anyone please help regarding this issue ...

CREATE TABLE ProjectsTable
(
  ProjectID     NUMBER(6) NOT NULL,
  ProjectName   VARCHAR2(200) NOT NULL,
  TotalCost      NUMBER(10,2),
  ExpenseTotal  NUMBER(10,2),
  CostRemaining     NUMBER(10,2),
  PRIMARY KEY (ProjectID)
);

CREATE TABLE ExpenseTable
(
  ID            NUMBER(6) NOT NULL,
  ProjectID     NUMBER(6) NOT NULL,
  ExpenseAmount NUMBER(10,2),
  ExpenseDate   NUMBER(4),
  CONSTRAINT fk
  FOREIGN KEY (ProjectID)
  REFERENCES ProjectsTable(ProjectID)  
); 

CREATE TRIGGER ExpenseSum AFTER INSERT ON ExpenseTable FOR EACH ROW
BEGIN
    UPDATE ProjectsTable P
    SET ExpenseTotal =  ExpenseTotal + :New.ExpenseAmount
    where P.ProjectID = :New.ProjectID;
END;
/
  • I know this isn't what you want to hear, but this is a flawed design on many levels. Never store a value (expense total) that can be computed at run time (sum(expense_table.expense_amount). Also, PL/SQL has no way of interacting with the client process, so you'd have to return some code to the client to inform it of the 'excess expense' condition and let the client inform the user. Plus, from a business angle, if the expense is being entered then presumably the expense has already been incurred, and so must be recorded, whether it exceeds budget or not. – EdStevens Oct 18 '17 at 1:05
  • Thanks for ur replay ... now what should I do ... How can I solve this? – Abu Abed Md. Shohaeb Oct 19 '17 at 8:30
  • How to solve it? Question the requirement (especially the requirement that a trigger gives a message) and Fix the design. Both the requirement and the design are fatally flawed. – EdStevens Oct 20 '17 at 13:49
  • One other flaw I just noticed. You have a column 'ExpenseDate' defined as a number. It should be a DATE. Never, ever store a date as anything but a DATE. – EdStevens Oct 20 '17 at 14:22
1

Here's a solution with a rational table design and reasonable reporting requirements.

SQL> -- create test tables
SQL> CREATE TABLE estevens.projects
  2  (
  3    project_id     NUMBER(6) NOT NULL,
  4    project_name   VARCHAR2(200) NOT NULL,
  5    project_budget NUMBER(10,2) not null,
  6    PRIMARY KEY (project_id)
  7  );

Table created.

SQL> 
SQL> CREATE TABLE estevens.project_expenses
  2  (
  3    expense_id        NUMBER(6) NOT NULL,
  4    project_id        NUMBER(6) NOT NULL,
  5    expense_amt       NUMBER(10,2),
  6    expense_date      DATE,
  7    CONSTRAINT project_expense_fk
  8    FOREIGN KEY (project_id)
  9    REFERENCES projects(project_id)
 10  );

Table created.

SQL> --
SQL> 
SQL> --  populate tables
SQL> insert into estevens.projects
  2           values (1,
  3               'DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.projects
  2           values (2,
  3               'ANOTHER DEMO PROJECT',
  4               1000.00
  5              )
  6  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (1,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (2,
  3               1,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> insert into estevens.project_expenses
  2           values (3,
  3               2,
  4               600,
  5               to_date('2017-10-19','yyyy-mm-dd')
  6               )
  7  ;

1 row created.

SQL> -- daily management report
SQL> col project_name for a10
SQL> select p.project_id,
  2         p.project_name,
  3         p.project_budget,
  4         sum(e.expense_amt) total_expense,
  5         case
  6           when sum(e.expense_amt) > p.project_budget
  7           then sum(e.expense_amt) - p.project_budget
  8           else null
  9         end OVER_BUDGET
 10  from estevens.projects p
 11  join estevens.project_expenses e on p.project_id = e.project_id
 12  group by p.project_id,
 13         p.project_name,
 14         p.project_budget
 15  order by p.project_id
 16  ;

PROJECT_ID PROJECT_NA PROJECT_BUDGET TOTAL_EXPENSE OVER_BUDGET
---------- ---------- -------------- ------------- -----------
         1 DEMO PROJE           1000          1200         200
           CT

         2 ANOTHER DE           1000           600
           MO PROJECT


2 rows selected.

SQL>  --
SQL> drop table estevens.project_expenses;

Table dropped.

SQL> drop table estevens.projects;

Table dropped.

SQL> --
SQL> spo off

You could also expand the idea to have an 'expense remaining' column.

Note that I have also replaced MS-style cAmElcAsE names with oracle-style names. When in Rome . . .

  • Thanks for your replay ... actually I want a trigger which should stop insert in expenses table when over budget. – Abu Abed Md. Shohaeb Oct 22 '17 at 4:19
  • Sometimes the solution is not technical. If the expense is being recorded, hasn't the expense already been incurred? Are you saying you want to prevent the posting of an event (incurring an expense) that has already occurred? What happens then? I'm thinking of this from a management view, not a coder's view. In any event, if this is really what you want, it should be coded into the procedure that is doing the insert, and the check should be made before the insert is attempted. And note my changes to your table design. Never store that which can be calculated. – EdStevens Oct 22 '17 at 12:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.