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

SQL Server Development (2000)

TRIGGER

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2004-08-11 : 10:00:26

I've created a trigger for update. However it is not working correct.
I would like to get the data before update and after update if more then 1 row updated. How do I do this?
CREATE TRIGGER dbo.mstr_Log ON mstr
FOR UPDATE
AS
BEGIN
-- Audit OLD record.
Insert Into mstr_Log
(user,Insert_date)
select id,'Old',getDate() from table

-- Audit NEW record.
Insert Into mstr_Log
(user,Insert_date)
select id,'New',getDate()from table
END

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-11 : 10:32:22

There's a fair bit of stuff on this topic if you search for something like 'Audit Trigger'.
However, a lot of links seem to be down at the moment, so here's an example:

--Create table
CREATE TABLE dbo.People
(
PersonID INT IDENTITY(1, 1),
FirstName NVARCHAR(50),
LastName NVARCHAR(50)
)

--Populate table
INSERT INTO dbo.People
(
FirstName,
LastName
)
VALUES
(
N'Mark',
N'Davidson'
)

--Create log table
CREATE TABLE dbo.People_Log
(
PersonID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
State VARCHAR(6),
DateOfChange DATETIME

)

--Trigger
CREATE TRIGGER TR_U_People
ON dbo.People
FOR UPDATE

AS
INSERT INTO
dbo.People_Log
SELECT
d.PersonID,
d.FirstName,
d.LastName,
'Before' AS State,
CURRENT_TIMESTAMP AS DateOfChange
FROM
deleted AS d
UNION
SELECT
i.PersonID,
i.FirstName,
i.LastName,
'After',
CURRENT_TIMESTAMP
FROM
inserted AS i
ORDER BY
d.PersonID,
State

--Update row
UPDATE
dbo.People
SET
FirstName = 'James'
WHERE
dbo.People.PersonID = 1

--Select from log
SELECT
pl.PersonID ,
pl.FirstName ,
pl.LastName ,
pl.State ,
pl.DateOfChange
FROM
dbo.People_Log AS pl

Mark

Kristen
Test

22859 Posts

Posted - 2004-08-11 : 12:59:07

CREATE TRIGGER dbo.mstr_Log ON mstr
FOR UPDATE
AS
BEGIN
-- Audit OLD record.
Insert Into mstr_Log
(user,Insert_date)
select id,'Old',getDate() from deleted

-- Audit NEW record.
Insert Into mstr_Log
(user,Insert_date)
select id,'New',getDate()from inserted
END

If you only want to log changes when MORE THAN one row is updated, then put the two INSERTs in:
IF (SELECT COUNT(*) FROM inserted) > 1 THEN
BEGIN
... inserts here
END

Note also that you are msising a column name
Insert Into mstr_Log
(user, ChangeType, Insert_date)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources