Nismo

怎麼去記錄妳異動的資料呢？

參考：http://msdn.microsoft.com/en-us/library/ms177564%28SQL.90%29.aspx

USE tempdb
go

CREATE TABLE table1
(
    id INT,
    employee VARCHAR(32)
)
go

INSERT INTO table1 VALUES(1, 'Fred')
INSERT INTO table1 VALUES(2, 'Tom')
INSERT INTO table1 VALUES(3, 'Sally')
INSERT INTO table1 VALUES(4, 'Alice')
GO

DECLARE @MyTableVar TABLE
(
    id INT,
    employee VARCHAR(32)
)

PRINT 'table1, before delete'
SELECT * FROM table1

DELETE FROM table1
OUTPUT DELETED.* INTO @MyTableVar
WHERE id = 4 OR id = 2

PRINT 'table1, after delete'
SELECT * FROM table1

PRINT '@MyTableVar, after delete'
SELECT * FROM @MyTableVar

DROP TABLE table1

怎麼知道你修改資料前後的值呢？

參考討論：http://www.sqlservercentral.com/Forums/Topic736225-1567-1.aspx

create table tb_User
(
    Name nvarchar(100)
)
insert into tb_User values('Assis Marques')

select * from tb_User
UPDATE a
SET Name = 'Assis Marques new'
OUTPUT Inserted.Name, Deleted.Name
FROM tb_User a
WHERE Name = 'Assis Marques'
select * from tb_User
drop table tb_User