sp_who2 @@spid
SELECT program_name
FROM sys.sysprocesses
WHERE spid = @@spid
CREATE TABLE test
(
id INT IDENTITY(1, 1)
PRIMARY KEY ,
name VARCHAR(20)
)
GO
INSERT INTO test
VALUES ( 'a' ),
( 'b' ),
( 'c' )
GO
DROP TABLE testlog;
CREATE TABLE testlog
(
lid INT IDENTITY(1, 1)
PRIMARY KEY ,
tid INT ,
tname1 VARCHAR(20) ,
tname2 VARCHAR(20) ,
type VARCHAR(20) ,
host_name VARCHAR(200) ,
program_name VARCHAR(200)
);
;
ALTER TRIGGER tr_getlog ON test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @program_name VARCHAR(200) ,
@host_name VARCHAR(200)
SELECT @program_name = RTRIM(program_name),
@host_name = RTRIM(hostname)
FROM sys.sysprocesses
WHERE spid = @@spid
IF @host_name = 'PC00425'
BEGIN
ROLLBACK;
RETURN;
END
INSERT testlog ( tid, tname1, tname2, type, host_name, program_name )
SELECT ISNULL(a.id, b.id) AS id, a.name, b.name,
( CASE WHEN b.name IS NULL
AND a.name IS NOT NULL THEN 'insert'
WHEN a.name IS NULL
AND b.name IS NOT NULL THEN 'delete'
ELSE 'update'
END ) AS type, @host_name, @program_name
FROM inserted a
FULL JOIN deleted b ON a.id = b.id
END
SELECT *
FROM test
DELETE TOP ( 1 )
FROM test
SELECT *
FROM testlog
SELECT program_name
FROM sys.sysprocesses
WHERE spid = @@spid
CREATE TABLE test
(
id INT IDENTITY(1, 1)
PRIMARY KEY ,
name VARCHAR(20)
)
GO
INSERT INTO test
VALUES ( 'a' ),
( 'b' ),
( 'c' )
GO
DROP TABLE testlog;
CREATE TABLE testlog
(
lid INT IDENTITY(1, 1)
PRIMARY KEY ,
tid INT ,
tname1 VARCHAR(20) ,
tname2 VARCHAR(20) ,
type VARCHAR(20) ,
host_name VARCHAR(200) ,
program_name VARCHAR(200)
);
;
ALTER TRIGGER tr_getlog ON test
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @program_name VARCHAR(200) ,
@host_name VARCHAR(200)
SELECT @program_name = RTRIM(program_name),
@host_name = RTRIM(hostname)
FROM sys.sysprocesses
WHERE spid = @@spid
IF @host_name = 'PC00425'
BEGIN
ROLLBACK;
RETURN;
END
INSERT testlog ( tid, tname1, tname2, type, host_name, program_name )
SELECT ISNULL(a.id, b.id) AS id, a.name, b.name,
( CASE WHEN b.name IS NULL
AND a.name IS NOT NULL THEN 'insert'
WHEN a.name IS NULL
AND b.name IS NOT NULL THEN 'delete'
ELSE 'update'
END ) AS type, @host_name, @program_name
FROM inserted a
FULL JOIN deleted b ON a.id = b.id
END
SELECT *
FROM test
DELETE TOP ( 1 )
FROM test
SELECT *
FROM testlog