Simple Trigger Issue?

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