Create 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

Transact-SQL (2000)

Create Trigger

Author

Topic

seyha_moth
Yak Posting Veteran

74 Posts

Posted - 2007-05-17 : 03:46:06

Dear sir or madam

I want to create trigger to get IP address,computer name and Date of user when they insert,update or delete data in table.

I look forward to hearing from you

Thank you in advance!

seyha moth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 03:47:55

IP address is not available.
computer name : host_name()
date : getdate()

seyha_moth
Yak Posting Veteran

74 Posts

Posted - 2007-05-17 : 03:51:32

I write the following trigger

CREATE TRIGGER [trX_ExamScore]
ON [dbo].[tblX_ExamScore]
FOR UPDATE
AS
INSERT INTO tblX_ExamScoreUpdated(ExamScoreID,SubjectExID,ScoreTypeID,Score,UpdateDate,Comment) Select del.ExamScoreID,del.SubjectExID,del.ScoreTypeID,Score,GetDate(),'Old'+host_name FROM DELETED del
INSERT INTO tblX_ExamScoreUpdated(ExamScoreID,SubjectExID,ScoreTypeID,Score,UpdateDate,Comment) Select ins.ExamScoreID,ins.SubjectExID,ins.ScoreTypeID,Score,GetDate(),'Update'+hostname FROM INSERTED ins

but they made error

Msg 207, Level 16, State 3, Procedure trX_ExamScore, Line 9
Invalid column name 'host_name'.

Can you help me?

seyha moth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 03:54:39

it is

host_name()

seyha_moth
Yak Posting Veteran

74 Posts

Posted - 2007-05-17 : 03:59:22

I corrected it from hostname to host_name but it still make the same error

seyha moth

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-17 : 04:01:46

[code]
CREATE TRIGGER [trX_ExamScore]
ON [dbo].[tblX_ExamScore]
FOR UPDATE
AS
INSERT INTO tblX_ExamScoreUpdated(ExamScoreID,SubjectExID,ScoreTypeID,Score,UpdateDate,Comment)
Select del.ExamScoreID,del.SubjectExID,del.ScoreTypeID,Score,GetDate(),'Old'+host_name()
FROM DELETED del

INSERT INTO tblX_ExamScoreUpdated(ExamScoreID,SubjectExID,ScoreTypeID,Score,UpdateDate,Comment)
Select ins.ExamScoreID,ins.SubjectExID,ins.ScoreTypeID,Score,GetDate(),'Update'+ host_name()
FROM INSERTED ins
[/code]

seyha_moth
Yak Posting Veteran

74 Posts

Posted - 2007-05-17 : 04:05:19

Thank you for your reply.Now, I can solve it with your help.

seyha moth

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources