delete trigger

alter  TRIGGER TRIGGER_MEMBER ON MEMBER
FOR DELETE
AS
IF (SELECT COUNT(*) FROM DELETED) > 0
BEGIN

 DECLARE @SPID INT = @@SPID
 DECLARE @SQL_HANDLE BINARY(20)
 DECLARE @TEXT VARCHAR(MAX)

 DECLARE @LOG_DELETE TABLE
 ( LOGINAME NCHAR(256)
 ,NT_USERNAME NCHAR(256)
 ,ACTION_DATE  DATETIME
 ,PROGRAM_NAME VARCHAR(2000)
 ,NET_ADDRESS NVARCHAR(48)
 ,NET_LIBRARY NCHAR(24)
 ,PROTOCOL_TYPE NVARCHAR(40)
 ,TEXT VARCHAR(MAX)
 )

 declare @sql_statement table (
 language_event NVARCHAR(100),
 parameters INT,
 event_info NVARCHAR(4000),
 event_time DATETIME DEFAULT CURRENT_TIMESTAMP);

 declare @dbcc_cmd nvarchar(2000)
 set @dbcc_cmd = 'DBCC INPUTBUFFER('+ cast(@SPID as varchar(200)) + ');'
 INSERT INTO @sql_statement (language_event, parameters, event_info)
 EXEC(@dbcc_cmd);

 SELECT @TEXT = event_info FROM @sql_statement

 INSERT INTO @LOG_DELETE
 SELECT
  LOGINAME
 ,NT_USERNAME
 ,GETDATE()
 ,PROGRAM_NAME
 ,CAST(CONNECTIONPROPERTY('LOCAL_NET_ADDRESS')  AS NVARCHAR(48))
 ,NET_LIBRARY
 ,CAST(CONNECTIONPROPERTY('PROTOCOL_TYPE') AS NVARCHAR(40))
 ,@TEXT
 FROM MASTER.DBO.SYSPROCESSES WHERE SPID = @SPID

 ROLLBACK TRANSACTION
 
 INSERT INTO JOYMDB.DBO.LOG_DELETE
 SELECT * FROM @LOG_DELETE

 exec joymdb.dbo.[핸드폰메세지_아이폰] 'delete from member', -1, 'lpoint1' , 'Y', 0

END
GO