x

How is log file created with trigger types ?

I have a table called employee with records as

EMPID EMPNAME EMPPH

1 Dicky 2154 2 Harry 2154 3 Loosy 2154

where my EMPID is primary key.

When I try to insert a new record with EMPID as 3 using TRIGGER. What will LOG file hold?

Please help me out, ping me back if question is not clear?

Thanks

Cheers Techno

more ▼

asked May 03, 2010 at 11:43 AM in Default

Techno gravatar image

Techno
33 3 3 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

A trigger won't affect what goes into the log. If you're manipulating data, a log entry is created, whether that's from a procedure or a trigger.

Maybe I don't understand the question.

more ▼

answered May 03, 2010 at 11:47 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98k 19 21 74

I think he's asking what gets logged on a key collision...
May 03, 2010 at 03:57 PM Matt Whitfield ♦♦
Ah, in the error log? That's totally different. Take it. I'm off to teach karate.
May 03, 2010 at 04:36 PM Grant Fritchey ♦♦

No No .. Let me repeat the question again. Consider the above records as stated in above update. Till this point of time my log file is empty or no log file is created. Now I execute AFTER trigger to insert a record with EMPID (this is primary key) as 3. Now here is my question: 1. Will Log be created. 2. If Log file is created what will Log file hold?

Hope I am clear to you at this point of time?
May 04, 2010 at 11:23 AM Techno
You need to define what you mean by log file. I think you're referring to either the transaction log or the error log. Those are the log files that are automatically a part of SQL Server. Anything else is something you're adding on. The error log will exist, but will not recieve anything. The transaction log will receive an entry for the insert statement.
May 04, 2010 at 11:40 AM Grant Fritchey ♦♦
I Got some answer from your updates. Thanks for it. Well yes I am talking about transaction log. As you said The transaction log will receive an entry for the insert statement. But that insert statement is failure, will it still hold these log?
May 05, 2010 at 12:44 PM Techno
(comments are locked)
10|1200 characters needed characters left

As Grant said, a trigger will not directly affect what goes into the log. If the trigger itself contains a logged command that will go in the log as normal.

One other thing I would note, I would expect a column named empid to have a unique index (perhaps primary key?) and it looks like you already have a #3 there. So if you tried to insert it, it might be rejected with a duplicate error.

more ▼

answered May 03, 2010 at 01:35 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

You are right, as you said I already have a #3 there. So my record insertion will fail. In this case what will my Log file generated. Consider the above records as stated in above update. Till this point of time my log file is empty or no log file is created.
May 04, 2010 at 11:26 AM Techno
Which log file are you referring to? If you mean the transaction log, then you already have it, and the insert command will be logged. Of course, depending on your recovery mode, it might also discard that entry immediately.
May 04, 2010 at 01:22 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x117
x65

asked: May 03, 2010 at 11:43 AM

Seen: 1520 times

Last Updated: May 03, 2010 at 11:48 AM