Insert Update Trigger

mstuart60 · September 26th, 2003, 03:36 AM

I need to put a trigger on a table to ensure that the date it was last modified is automatically put in the column Last_Mod and also the name of the user who modified the record into the column updated_by. Can someone please help me with the syntax.

Many thanks

Mike

mstuart60 · September 26th, 2003, 04:11 AM

Further to this. When I do put a trigger on this table then it stops me from updating and inserting new records. Its very strange. When I take the Trigger off I can now insert and update records. Is there a lock happening somewhere that is not llowing me to insert or update.

Mike

Jeff Mason · September 26th, 2003, 08:16 AM

Quote:

quote:Originally posted by mstuart60
... When I do put a trigger on this table then it stops me from updating and inserting new records. ...

Post the code for trigger you tried.

There is nothing inherent in a trigger which "stops" you from doing an insert, unless, of course, you have defined an INSTEAD OF trigger which executes, er, instead of, the UPDATE query which fires it.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mstuart60 · September 26th, 2003, 09:10 AM

Jeff have used the following code.
CREATE TRIGGER lastmod
ON tbl_session_booking
after INSERT, update
AS
insert into tbl_session_booking (last_mod, Updated_by)
Values (getdate(), suser_sname())

select last_mod, updated_by from Inserted

But when I input a record set I get a warning coming up that I cant insert nulls into one of the fields. Is it my select Statement that is wrong for the trigger.

Thanks
Mike

Jeff Mason · September 26th, 2003, 09:32 AM

Why are you INSERTing another row inside your trigger? In your original post, you indicated you wanted to update rows to add a last modified date and User name. The error occurs because your INSERT statement isn't complete - an INSERT inserts an entire new row and you must have some non-null columns defined in your table which your INSERT statement in the trigger isn't supplying a value for.

Your trigger should UPDATE the rows in the base table as identified by the 'inserted' table, as, for example:

Code:

UPDATE tbl_session_booking
    SET last_mod=current_timestamp,
        Updated_by=suser_sname()
    FROM tbl_session_booking INNER JOIN inserted
        ON tbl_session_booking.<primarykeycolumn>=inserted.<primarykeycolumn>;

where <primarykeycolumn> is the primary key of your table.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mstuart60 · September 26th, 2003, 09:58 AM

Jeff.

I have the following columns in that table

booking_id, course_number, student_id, Session_date

Mike

Jeff Mason · September 26th, 2003, 10:47 AM

Now you've thoroughly confused me.

Where are the 'Last_Mod' and 'Updated_by' columns mentioned in your original post?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mstuart60 · September 26th, 2003, 10:51 AM

Jeff,

the columns are in the table i should have included them in the last reply.

booking_id, course_number, student_id, session-date, last_mod, updated_by

sorry for the confusion.

Mike

Jeff Mason · September 26th, 2003, 11:45 AM

OK. So, the trigger should do an UPDATE as I described above, and not an INSERT as you attempted.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

mstuart60 · September 30th, 2003, 03:52 AM

Jeff,

I must be doing something far wrong. The primary key for the table is booking_id. Can you be so kind to write the trigger out in full that I should put in.
I keep getting an error that it does not recognise column tbl_session_booking.

Many Thanks

Mike