problem regarding update after in a trigger for automatic update of date

[danishahmed2007]

i have a table of the format

CREATE TABLE testing
(
emp_name varchar(50),
emp_id int,
rec_up datetime DEFAULT (getdate())
)

and on the event of an update to any column in the table i want to
update the "rec_up" field with the current date by firing the following trigger

CREATE TRIGGER trig
ON testing
AFTER UPDATE
AS
IF(UPDATE(emp_n ame) OR UPDATE(emp_id))
BEGIN
UPDATE testing
SET rec_up = getdate()
END

My problem is that this trigger updates all the records with the getdate function
but i want only the record that is updated its 'rec_up' field be updated

Please help

[ck9663]

i have a table of the format

CREATE TABLE testing
(
emp_name varchar(50),
emp_id int,
rec_up datetime DEFAULT (getdate())
)

and on the event of an update to any column in the table i want to
update the "rec_up" field with the current date by firing the following trigger

CREATE TRIGGER trig
ON testing
AFTER UPDATE
AS
IF(UPDATE(emp_n ame) OR UPDATE(emp_id))
BEGIN
UPDATE testing
SET rec_up = getdate()
END

My problem is that this trigger updates all the records with the getdate function
but i want only the record that is updated its 'rec_up' field be updated

Please help

why don't you just include the rec_up field on your update statement and save the use of trigger. i think i'll be more efficient that way

[danishahmed2007]

why don't you just include the rec_up field on your update statement and save the use of trigger. i think i'll be more efficient that way

Actually my application is related to the database containing this kind of tables
Here the user does not know about the "rec_up" field and only updates the other fields. I want the "rec_up" field to get updated automatically with the current datetime whenever their is an update or insert in the table without the data entry person knowing about it.
So can you please give a solution to the trigger problem

[amitpatel66]

Actually my application is related to the database containing this kind of tables
Here the user does not know about the "rec_up" field and only updates the other fields. I want the "rec_up" field to get updated automatically with the current datetime whenever their is an update or insert in the table without the data entry person knowing about it.
So can you please give a solution to the trigger problem

Create a ROW LEVEL trigger and for each row, when any update or insert happens, set rec_up to current time.

Your trigger seems to be statement level. make it row level and include one more WHERE condition in your update statement, something like this:

UPDATE <table_name> SET rec_up = current_date()
WHERE empno = :new.empname OR empid:= new.empid
 

[danishahmed2007]

Create a ROW LEVEL trigger and for each row, when any update or insert happens, set rec_up to current time.

Your trigger seems to be statement level. make it row level and include one more WHERE condition in your update statement, something like this:

UPDATE <table_name> SET rec_up = current_date()
WHERE empno = :new.empname OR empid:= new.empid
 

Hi there thanks for the help but i think ms sql server does not have row level trigger support like in oracle.
But in the meantime i tried the following query . But still i could not get the desired result. Here i have used the inserted and deleted temporary tables

CREATE TRIGGER trig
ON testing
AFTER UPDATE
AS
IF(UPDATE(emp_id) or UPDATE(emp_name))
   UPDATE testing
   SET rec_up =( SELECT getdate() 
                       FROM INSERTED i,DELETED d
                       WHERE i.emp_id<>d.emp_id OR i.emp_name<>d.emp_name)
 

[amitpatel66]

Hi there thanks for the help but i think ms sql server does not have row level trigger support like in oracle.
But in the meantime i tried the following query . But still i could not get the desired result. Here i have used the inserted and deleted temporary tables

CREATE TRIGGER trig
ON testing
AFTER UPDATE
AS
IF(UPDATE(emp_id) or UPDATE(emp_name))
   UPDATE testing
   SET rec_up =( SELECT getdate() 
                       FROM INSERTED i,DELETED d
                       WHERE i.emp_id<>d.emp_id OR i.emp_name<>d.emp_name)
 

Oh ok. Then when you do an update on any record, then does it get stored in INSERTED virtual table?

When you do an insert use the below query for updating rec_up:
Check whether to select system date ,it is current_date() or currentdate()

 
UPDATE testing
   SET rec_up =current_date() 
 WHERE emp_id = (SELECT emp_id FROM INSERTED);