Capture MAC address in trigger

I have a simple trigger that writes out records to another table when a user
deletes a record. I am looking for a way to capture the Network Address valu
e
that is associated with the process. Is that possible ?
Here is the trigger. I want to insert the Network Address value into the
Lastuser coulumn in the Contact1del table
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
ThanksHOST_NAME() maybe?|||jenks wrote:
> I have a simple trigger that writes out records to another table when
> a user deletes a record. I am looking for a way to capture the
> Network Address value that is associated with the process. Is that
> possible ?
> Here is the trigger. I want to insert the Network Address value into
> the Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT, TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2,
> EXT3, EXT4, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY,
> DEAR, SOURCE, KEY1, KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES,
> CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1,
> KEY2, KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid
This will give you the mac address for the current spid. In your case,
you could use an embedded SELECT or grab the mac address before the
insert. For example:
Insert into #ttt (col1, col2)
Select 1, (
Select
net_address
From
master..sysprocesses
Where
spid = @.@.spid )
David Gugick - SQL Server MVP
Quest Software|||this might help:
select net_address from master..sysprocesses where spid=@.@.spid
dean
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:8B260AE5-DAF4-43CD-8A29-9232573A39EE@.microsoft.com...
>I have a simple trigger that writes out records to another table when a
>user
> deletes a record. I am looking for a way to capture the Network Address
> value
> that is associated with the process. Is that possible ?
> Here is the trigger. I want to insert the Network Address value into the
> Lastuser coulumn in the Contact1del table
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> Thanks
>|||Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
problem with that ? Thanks again!!
insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
where spid=@.@.spid), GETDATE(), LASTTIME, RECID
from deleted|||I ordinarily advise against using WITH(NOLOCK), because it can cause queries
to return incorrect answers, but this is a special case in which the row in
sysprocesses tied to @.@.SPID cannot be changed by activity on any another
spid, so it makes better sense to eliminate the extra shared lock.
I would save off net_address and GETDATE() in local variables before
executing the INSERT. That minimizes the number of database accesses and
could make it possible to identify what was changed by the statement that
caused the trigger to fire. Of course, that would work only if you stored
@.@.SPID, too.
DECLARE @.net_address nchar(12), @.getdate DATETIME
SELECT @.net_address = net_address, @.getdate = GETDATE() from
master..sysprocesses WITH(NOLOCK) WHERE spid = @.@.SPID
"jenks" <jenks@.discussions.microsoft.com> wrote in message
news:F382F996-E346-4815-ACCF-42AD3845008F@.microsoft.com...
> Awesome. Thanks everyone. I modified the trigger as follows. Anyone see a
> problem with that ? Thanks again!!
> insert into Contact1DEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME,
> DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4,
> ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, (select net_address from master..sysprocesses
> where spid=@.@.spid), GETDATE(), LASTTIME, RECID
> from deleted
>
>