TRIGGER FOR INSERT,UPDATE,DELETE

[Harish]

I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH

[Ivo]

Hi Harish,
You must create trigger for each activity, syntax is following :
CREATE TRIGGER Harrish_INSERT ON Table1 FOR INSERT <or UPDATE, DELETE>

In a trigger you can use clause IF UPDATE(column name) which tells you if that column was changed or not. You can also use logical table inserted (or deleted with delete trigger) which contains new values(or old in deleted) for rows being updated.

So if you want to track which user has changed a row you must write trigger and get user&#39;s name from somewhere (for example from sysobjects table) - you should know where to get it - and write data about changed row into your auditing table.
Enough ? If not, write me ...
Ivo


------------
Harish at 8/15/01 2:36:35 PM

I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH

[Dilip Vyas]

Hi, Harish

You can write the trigger on table s1 for both ways, eighter single trigger or in separate trigger. but it is always better to write the trigger for a specifict operation like insert, update, delete.

We have a psuedo tables named inserted and deleted in SQL server, we can use that table to update the second table in insert, update, delete trigger respectively.

if you need exact help for writing the triggers, let me know, I will help you.


Regards
Dilip Vyas
SQL Server DBA




------------
Harish at 8/15/01 2:36:35 PM

I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING.
I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS
LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE.
CAN SOMEBODY HELP IN WRITING THAT TRIGGER..?
HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.

DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE
SINGLE TRIGGER..?

PLEASE SUGGEST ME..ITS URGENT.

THANKS IN ADVANCE
HARISH