#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0

    Modify one table to trigger other tables


    Hi!

    We are running an Oracle 10g server.

    We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.

    I can via SQL add a part to the bill of materials and reserve the part...
    But then the part needs to be issued to make it disappear from the stock...

    I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part.

    The table is updated, and via SQL everything looks fine, but the part doesnt disappear from the stock...

    So my guess is that there is a trigger or function somewhere that I need for this...

    I know there isnt a lot of information for you to go on, but im not sure what information you need, so if there is anything you need that will help you to help me, please tell me

    Thanks a lot for your help!
    You will surely save my day!

    /Niklas
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    my guess is that there is a trigger or function somewhere that I need for this...
    That would seem to be correct.
    However, to offer anything more meaningful in the way of advice I think we need, at least,
    the structure of the tables involved.

    Clive
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by clivew
    That would seem to be correct.
    However, to offer anything more meaningful in the way of advice I think we need, at least,
    the structure of the tables involved.

    Clive
    Many thanks for the answer! iīll try to be as thorough as I can in my description, please ask if there is anything else you need to know

    We have one table called Parts Master that contains all the parts in our system, with part numbers, description and such..
    Then we have a table called Stock that represent each fysical part... eg. each bolt has a different row in the stock table...
    It contains a foreign key to the parts master table to show what part it is...

    Then we have a table called WO_OPERATION where each row represents one work order. Then we have a WO_TASK where each row represents one task (one work order may have many tasks)...

    Then we have the table WO_BOM... each task can contain many bill of materials... each row in the WO_BOM contains information about what part is on the bill of material and what qty is needed, what work order it is connected to, the reserved qty, the issued qty and so on (the qty reserved & qty issued is always 0 in the beginning)

    Those are the obvious tables connected to this problem...

    But we also have tables like STOCK_RESERVATIONS that contains foreign keys to the work order table, the bill of material tables, and also contains the qty reserved column.
    This table also contains the QTY_ISSUED column, and also a foreign key to an table that i find interesting... the STOCK_TI table that contains the specific stockline (the stock table) and also issued_qty and also tran_date .

    It also has a column named GL_TRANS_LINK that contains something that seems to be an foreign key to another table... but there is no table called GL_TRANS_LINK, however there is a table called GL_TRANS so I guess thatīs the table... however that table contains 0 rows...

    I have tried to enter a new row in STOCK_TI but it says that it needs a reference in GL_TRANS_LINK...

    I guess thats the information I can think of right now, if there is anything else that might help you, just tell me
    and once again, thanks for your help!
    /Niklas
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    You can list the triggers on your tables to determine which one is/is not firing with:
    Code:
    select *
     from  sys.all_triggers
    where  owner       =  'OWNER'
     and   table_name  =  'TABLE_NAME'
    
    select *
     from  sys.all_trigger_cols
    where  trigger_owner  =  'OWNER'
     and   table_name     =  'TABLE_NAME'
    
    select *
     from  all_source
    where  owner = 'OWNER'
     and   name  = 'TRIGGER_NAME'
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by spacebar208
    You can list the triggers on your tables to determine which one is/is not firing with:
    Code:
    select *
     from  sys.all_triggers
    where  owner       =  'OWNER'
     and   table_name  =  'TABLE_NAME'
    
    select *
     from  sys.all_trigger_cols
    where  trigger_owner  =  'OWNER'
     and   table_name     =  'TABLE_NAME'
    
    select *
     from  all_source
    where  owner = 'OWNER'
     and   name  = 'TRIGGER_NAME'
    I have a list of triggers that is visible in the SQL Developer, but their names are not exactly easy to understand...
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    The names do not matter.
    What matters for a trigger is:
    1. What table it is attached to.
    2. What it does. (i.e. the code)
    3. When it does it. (insert, update, delete - before,after)
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by clivew
    The names do not matter.
    What matters for a trigger is:
    1. What table it is attached to.
    2. What it does. (i.e. the code)
    3. When it does it. (insert, update, delete - before,after)
    Yes I understand that
    But to my low grade of knowledge I first go by the name, since there are a lot of triggers in the database and Im not sure what trigger to look at... And it will take a lot of time to go through the code of every trigger...

    I thought it might be an easier way?
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    Originally Posted by Nigge
    Yes I understand that
    But to my low grade of knowledge I first go by the name, since there are a lot of triggers in the database and Im not sure what trigger to look at... And it will take a lot of time to go through the code of every trigger...

    I thought it might be an easier way?
    Well, you seemed to know that from the start.
    I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesnt disappear from the stock...
    Presumably you need to add (or enhance if one already exists) the after update trigger on the table that contains the QTY_ISSUED column to adjust the stock quantity as desired.

    Clive
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    To start with, i really appreciate you guys helping me!
    you guys rock!

    The thing is, there IS either a trigger or a function for this...
    our information system does this function (well daaaaaaaah ;-) )
    I have tried setting up a trace session for this but the trace file was all cluttered up with nonsence... so it was almost impossible to read it...

    So I doubt I have to create a trigger... just find the one that is being used...
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Hm, one question... is there any way I can ensure that a trigger has been fired?...
    I have a few triggers I think might be what im after, but i dont know if they fires when i enter things into this table...

    is there anyway to "force" them to fire? or to see if they are activated?

    Thanks again
    /N
  20. #11
  21. Humble Learner
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2007
    Location
    Bangalore, India
    Posts
    280
    Rep Power
    120
    Try this

    Code:
    SELECT trigger_name,trigger_type,triggering_event,table_owner,
    base_object_type,table_name,when_clause,status,trigger_body
    FROM USER_TRIGGERS WHERE STATUS = 'ENABLED'
    check for your source table
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    The thing is, there IS either a trigger or a function for this...
    our information system does this function (well daaaaaaaah ;-) )
    Well, that would lead me to believe that it is not a trigger.
    A (correctly implemented) trigger should always maintain the integrity of the data.

    I would suspect, from your comment, that the integrity is being maintained either by
    the data being managed by stored procedures called by your application (or a middle tier)
    rather than raw SQL or by code in the application itself.

    With what (little) we know of your universe, it is hard to be more specific.

    I do not know what tools, or budget for tools, you have available; but various tools like
    PL/SQL Developer or TOAD etc. make extracting and searching all the PL/SQL in your schema easy.

    You may need to consider extracting it all and then searching for references to the columns of interest
    to establish where and how they are touched.

    PL/SQL Developer is very affordable and I think it is downloadable with a limited free trial period.

    Full Disclosure
    I am a long time user of PL/SQL Developer with absolutely no financial interest in it.

    Clive
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    7
    Rep Power
    0
    Originally Posted by clivew
    Well, that would lead me to believe that it is not a trigger.
    A (correctly implemented) trigger should always maintain the integrity of the data.

    I would suspect, from your comment, that the integrity is being maintained either by
    the data being managed by stored procedures called by your application (or a middle tier)
    rather than raw SQL or by code in the application itself.

    With what (little) we know of your universe, it is hard to be more specific.

    I do not know what tools, or budget for tools, you have available; but various tools like
    PL/SQL Developer or TOAD etc. make extracting and searching all the PL/SQL in your schema easy.

    You may need to consider extracting it all and then searching for references to the columns of interest
    to establish where and how they are touched.

    PL/SQL Developer is very affordable and I think it is downloadable with a limited free trial period.

    Full Disclosure
    I am a long time user of PL/SQL Developer with absolutely no financial interest in it.

    Clive
    I am currently using Oracle SQL Developer Im not sure if thats then one you are after or if there is another one called PL/SQL Developer?

    I am more than willing to try... =)
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I am currently using Oracle SQL Developer Im not sure if thats then one you are after or if there is another one called PL/SQL Developer?
    I don't use Oracle SQL Developer myself, so I am not familiar with its features.

    Whatever you use, the requirement is either that you can easily search all the code in the schema
    for references to the column(s) of interest or that you can export all the code in the schema
    to a text file that you can then search with any appropriate text editor you own.

    FYI: PL/SQL Developer is from Allround Automations.
    You can use a search engine to find them if needed.

    Clive

IMN logo majestic logo threadwatch logo seochat tools logo