Win a copy of Secure Financial Transactions with Ansible, Terraform, and OpenSCAP this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Rob Spoor
  • Henry Wong
  • Liutauras Vilda
Saloon Keepers:
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • Tim Holloway
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh
  • Jj Roberts

Call function in trigger

 
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So i have a function that returns saldo. It looks like this:



My trigger should call get_saldo and check that the amount of money being inserted in the withdraw table is not more than money on the account. My trigger looks like this so far:


Hope you guys understand what i mean. And i know what this forum thinks about stored procedures but i really need to use it. Its a school task where we need to use stored procedures.
 
Saloon Keeper
Posts: 23441
159
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It looks to me like you need the total amount of money in the account, but I don't see the "sum" function being used,

In other words, I expect the code should read more like this:


Note that I coded this in a way that only needed 1 function (the trigger) because the other function looked like it probably was equivalent to the SELECT.
 
Hugo Nilsson
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I tried this and get error on the select. I also tried putting get_saldo in the sum expression and then got error on sum
 
Tim Holloway
Saloon Keeper
Posts: 23441
159
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I can't give you working code, just suggestions. Not only do I use stored procedures only infrequently, I don't have a copy of Oracle handy, and the native stored procedure languages differ too much from one database to another for me to try something on one of my PostgreSQL or MySQL databases for example. One of the reasons, in fact, why I discourage stored procedures - they lock you in.

However, I gave you a bad select. A more valid SELECT would be:

You may have to SELECT INTO, since you're in a stored procedure, but I'm not sure about that.
 
It is no measure of health to be well adjusted to a profoundly sick society. -Krishnamurti Tiny ad:
SKIP - a book about connecting industrious people with elderly land owners
https://coderanch.com/t/skip-book
reply
    Bookmark Topic Watch Topic
  • New Topic