TRIGGER HELP

[baiju]

hii all ,

I WANT TO WRITE A TRIGGER WHEN UPDATING A PARTICULAR COLUMN IN A SQL SERVER DATABASE.HOW CAN I IDENTIFY THAT UNIQUE ROW.
PLEASE HELP

 

[bperry]

It sounds liek you want to know if a particular column was updated. Is that right? If so, you can use the UPDATE() function in your trigger. Something like this:
[tt]
CREATE TRIGGER MyTrigger
ON YourTable
FOR UPDATE
AS
IF UPDATE(PayRate)
BEGIN
do some code
END
-----------------
[/tt]

That's just a rough outline, and needs to be adapted to your own situation.

If's that is not what you were thinking, you need to provide more details and comments.

 

[bperry]

You need to describe more what you are meaning by "How can I identify that unique row?"

 

[tlbroadbent]

SQL Server Triggers have two unique objects that are not available except in Triggers. These are virtual tables named deleted and inserted. The deleted table contains rows that were deleted or the rows prior to update. The inserted table contains inserted rows or rows after the update.

These tables contain all of the columns of the table being updated. Thus if the table has a primary or unique key, say RecID, you can find the RecIDs in the Inserted table and have a unique key for each updated row.

You can extract a RecID with code like the following.

Declare @id int
Select @id=RecID From Inserted

Of course this only works if one records is updated unless you create a loop in code. You can also use the vitual tables in queries.

Archive records that have been changed;
Insert Into HistTable
Select * From Deleted

Update another table based on the Inserted table.

Update InventoryTbl
Set Qty = Qty - i.ShipQty
From InverntoryTbl t
Join Inserted i
On t.PartId=i.PartID

I hope this helps. Is it what you need to know? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.