• Are you aware that your trigger will not work if someone inserts multiple rows into the Project table in a single transaction. For instance, try it with something like this:

    INSERT INTO PROJECT(ID, ClientCode, ProjectName)

    SELECT ID, ClientCode, ProjectName

    FROM

    (

    SELECT 5, 'MIZ', 'Project 1'

    UNION ALL

    SELECT 6, 'MIZ', 'Project 2'

    UNION ALL

    SELECT 7, 'MIZ', 'Project 3'

    )

    Unless you are restricting how the Project table can be built to ensure that entries are always added one at a time, you are going to have problems.

    Also, I suspect that you will regret doing file operations in the trigger. For one thing, anything which might cause an error in the trigger is going to cause the INSERT operation on PROJECT to get rolled back (and if it was part of a larger operation, that might get rolled back, etc.). That may be how you want it to work, but I doubt it. The file operations are also likely to have a lot more things which might delay them than the database and anything which delays a file operation is going to delay your database transaction. Unless you absolutely need the database and file operations to be synchronous, I suspect that you would be a lot better off using the trigger to create a queue of file operations which are needed and have a separate process which periodically checks the queue for new work and, when it finds entries to process, creates the appropriate directories.

    Even if you, ultimately, decide to keep doing the file operations as part of the trigger, you need to address the issue of the trigger not working with multirow inserts.

    - Les