Forum Stats

  • 3,839,096 Users
  • 2,262,451 Discussions
  • 7,900,858 Comments

Discussions

Simple Trigger Issue?

2»

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 3, 2019 7:53AM

    Why do you need a trigger and an "audit" table at all?  Looks to me like you could just add a status column to your 'consultants' table.

  • JazzyB
    JazzyB Member Posts: 8
    edited Jan 3, 2019 7:57AM

    This is simply to demonstrate how a trigger can be used so that a consultant must gain approval from an admin.

    Consultants are using an apex application to view their own data, and to request a change to either name or postcode.

    So it's an admin that allows these by changing the value from Pending to Authorised.

    Just for an assignment!

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jan 3, 2019 8:01AM
    JazzyB wrote:This is simply to demonstrate how a trigger can be used so that a consultant must gain approval from an admin.Consultants are using an apex application to view their own data, and to request a change to either name or postcode.So it's an admin that allows these by changing the value from Pending to Authorised.Just for an assignment!

    Oh, so the whole thing is a homework question . . . .

  • JazzyB
    JazzyB Member Posts: 8
    edited Jan 3, 2019 8:02AM

    I honestly tried so many methods I don't remember haha, only been learning SQL just shy of a year so I still make them mistakes.

    Basically I want it so that the update will take place on the consultant table once that 'Pending' in the audit table has been changed to 'Authorised'.

    At the moment, the consultant's changes are being immediately updated.

    So I'm unsure how to do this as the 'Pending' value is in a separate table.

    Hope this makes sense!

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Jan 3, 2019 8:07AM Answer ✓
    JazzyB wrote:Thanks for the response,It was that semicolon and after INSERT was changed to after UPDATE as I'm attempting to demo on existing dataA silly mistake but now works.I am attempting to only let these changes pass if the status in the audit table is changed to 'Authorised'Is this possible by using an if statement within the trigger?Or does a seperate BEFORE UPDATE trigger need to be made for my consultant table?Thanks again

    I don't see how this would work, the update must happen in order for the trigger to fire...

    If you want to add an authorization step to updates then you might want to consider:

    Create a staging table with the same columns and primary key, add additional column(s) for authorization status (and authorized by person etc)

    Create a view on this table which doesn't include authorization columns and also filters out rows that have been authorized. Grant inserts,deletes and select on this view by the users that you don't wish to update the original table directly but do wish to be able to get their changes authorized.

    Grant updates to the staging table to users that can authorize, you could use a trigger here if you wanted to make sure that they set an authorized by person to themselves.

    Write a procedure that is owned by the owner of the main table that merges into the main table selecting only authorized changes from the staging table, and then deletes the staging rows that were used.

    -edit

    Or instead of deleting rows from the staging table you can archive them off into an audit table

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Jan 3, 2019 8:10AM
    JazzyB wrote:I honestly tried so many methods I don't remember haha, only been learning SQL just shy of a year so I still make them mistakes.Basically I want it so that the update will take place on the consultant table once that 'Pending' in the audit table has been changed to 'Authorised'.At the moment, the consultant's changes are being immediately updated.So I'm unsure how to do this as the 'Pending' value is in a separate table.Hope this makes sense!

    So you want to do the update, but have the update float in some limbo somewhere until the audit table is updated?

    That's not going to work, certainly not with triggers. The effects of the trigger are part of the same transaction that caused the trigger to fire. All the changes have to be either committed or rolled back together and no other user can see the audit record to approve it until the commit happens.

    Look at Andrew's suggestion in reply #15 - you'll need something along those lines, not triggers.

  • JazzyB
    JazzyB Member Posts: 8
    edited Jan 3, 2019 8:35AM

    Thanks for such an insightful response

    I'll look into this and can hopefully get it working

    Much appreciated!