What's the way to go when inserting values to multiple tables?

Question

I'm inserting some values onto employees table using this in the controller:

            $this->Employee->create();
            if ($this->CoreProgram->save($this->request->data)) {
                $this->Session->setFlash('Program has been added.');
                $this->redirect(array('action' => 'index'));
            } else {
                $this->Session->setFlash('Unable to add record.');
            }

However, I want to insert a record to another table audit. More specifically, the current date along with employee_id field that I used for employees. What is the best approach to do this? Should I just create $this->Audit->create();? Or is there a better way?

Answer 1

Use an insert trigger on employees table that inserts audit entry into the audit table:

CREATE TRIGGER tr_employees_ins ON dbo.employees FOR INSERT AS  
BEGIN  

    INSERT dbo.audit( [op] ... employee_id ...)
    SELECT 'INS' ... employee_id
    FROM Inserted

END