update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update Trigger

Author

Topic

Avatar
Starting Member

2 Posts

Posted - 2004-05-27 : 11:17:30

hi folks.

Í want to do this:
/***************************************/
CREATE TRIGGER TRGfechaModificacion
ON UsuariosVencimientos
AFTER UPDATE AS
DECLARE @updatedID AS int
BEGIN
--Get the last ID updated
SELECT @updatedID = @@updated --???
-- Updated this record with the actual datetime
UPDATE
UsuariosVencimientos
SET
Fecha_Actualizacion = getDate()
WHERE
id = @updatedID
END
/***************************************/

Resume:

I want to get the 'id' updated so I can used for the update query

<b>Greetings</b>
-------------------

mtomeo
Starting Member

30 Posts

Posted - 2004-05-27 : 12:13:00

ASSuming updateID is an identity column, grab the value of @@Identity after your Insert:

INSERT INTO UsuariosVencimientos (....)
VALUES (....)
SELECT @@IDENTITY AS 'updateID'

A trigger would be overkill if it's an Identity column. If it's not an Identity column and the data from the Insert isn't available to you (IE the insert is generated from differenct applications or whatnot), just grab the value of updateID from the "inserted" table (Do a search on "CREATE TRIGGER" in BOL for more help).

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-05-27 : 12:49:52

When you work with triggers, you always need to keep in mind that the trigger fires once per DML, not once per row.


CREATE TRIGGER TRGfechaModificacion
ON UsuariosVencimientos
AFTER UPDATE AS
begin
update
    uv
set
    Fecha_Actualizacion = getDate()
from
    UsuariosVencimientos uv
    inner join inserted i
        on uv.id = i.id -- (Assuming id is your PK)
end

Jay White
{0}

Avatar
Starting Member

2 Posts

Posted - 2004-05-27 : 16:31:36

Thank you all.

Your solutions works fine ;)

Greetings
-------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources