quote:

---

Originally posted by joey ng

i am using the instead of update trigger to update the data and the format of the trigger is like below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE TRIGGER [dbo].[T_U_employee] ON [dbo].[employee]
INSTEAD OF UPDATE
AS

DECLARE @lcsql VARCHAR(max), @lddate DATETIME

SET @lcsql = ''

SELECT * INTO #inserted_tmp FROM inserted
CREATE INDEX inserted_tmp ON #inserted_tmp (id)

IF UPDATE(id)
SET @lcsql = @lcsql + ' employee.cnoee = #inserted_tmp.id,'

IF UPDATE(fname)
SET @lcsql = @lcsql + ' employee.fname = #inserted_tmp.fname,'

IF UPDATE(lname)
SET @lcsql = @lcsql + ' employee.lname = #inserted_tmp.lname,'

IF UPDATE(address)
SET @lcsql = @lcsql + ' employee.address = #inserted_tmp.address,'
.
.
.
.
SET @lcsql = 'Update employee SET ' + left(@lcsql, len(@lcsql) -1) + ' from employee, #inserted_tmp where employee.id=#inserted_tmp.id

EXEC (@lcsql)


With this trigger,if i just want to update the address, it also will run the other data field althought the other data field didnt need to update.So, is it have any method to update the changed data field?

---

quote:

---

Originally posted by joey ng

ok..when we update the data is using the application which inner connect to the database,so after we compute the record, it will update and insert the new record into the particular table.So we need to use the update and insert trigger to update and insert the data.But we realise the update data was spend many time to update record and it is impact the performance...now i am finding some method to solve this problem...

---

quote:

---

Originally posted by joey ng

yup,we must use the update trigger to update the record and use the application to insert and update data.For the example i hav a table "employee" ,inside the employee have fname,lname,id,address.But the application maybe will update the "id" data field and the other data field's value is no changed.

---

quote:

---

Originally posted by joey ng

yup, we use the hardcoded value to update.

---

UPDATE t
SET t.fname=Yourfnamevalue,
    t.lname=yourlnamevalue,
    t.address=youraddressvalue
FROM YourTable t
INNER JOIN INSERTED i
ON i.id=t.id

quote:

---

Originally posted by joey ng

ok..thanks..if we use the other way to update the record such as the value is retrieve from the screen then any method to solve the same problem?

---

quote:

---

Originally posted by joey ng

what i mean is the value is retrieve from screen which user key in.

---

UPDATE t
SET t.fname=@fname,
    t.lname=@lname,
    t.address=@address
FROM YourTable t
WHERE t.id=@id

quote:

---

Originally posted by joey ng

Finally I solve it...Thanks visakh16...you really help me a lot..

---

quote:

---

Originally posted by joey ng

After test from screen was fail again...the below is the trigger command which i write

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[T_U_employee] ON [employeee]
INSTEAD OF UPDATE
AS

DECLARE @id VARCHAR(128)
DECLARE @fname VARCHAR(128)
DECLARE @salary VARCHAR(128)

Update Dxpayenc

SET employee.id =@id,

employee.fname=@fname,

employee.salary=@salary

from employee
where employee.id=@id

---

quote:

---

Originally posted by joey ng

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[T_U_employee] ON [employeee]
INSTEAD OF UPDATE
AS

DECLARE @id VARCHAR(128)
DECLARE @fname VARCHAR(128)
DECLARE @salary VARCHAR(128)

Update employee

SET employee.id =@id,

employee.fname=@fname,

employee.salary=@salary

from employee
where employee.id=@id

sorry,the dxpayenc is i type wrongly....now i correct it,the value of id is user key in de..

---