[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Grab text of update query in trigger

mikeg

3/9/2007 4:37:00 PM

I have been putting together my first ever trigger and it has gone well. I
am essentially recording changes made to a table for auditing purposes.
However, I created it on a table of a third party app over which I have no
access to the code. One of the things I want to record is from which
application window the update statement was issued. Since the application
doesn't pass this information, I thought that perhaps I could analyze the
calling query from within the trigger and make a determination from that.

Is there a way to reference the text of the update query within a trigger?
5 Answers

EMartinez

3/10/2007 5:28:00 AM

0

On Mar 9, 10:37 am, MikeG <M...@discussions.microsoft.com> wrote:
> I have been putting together my first ever trigger and it has gone well. I
> am essentially recording changes made to a table for auditing purposes.
> However, I created it on a table of a third party app over which I have no
> access to the code. One of the things I want to record is from which
> application window the update statement was issued. Since the application
> doesn't pass this information, I thought that perhaps I could analyze the
> calling query from within the trigger and make a determination from that.
>
> Is there a way to reference the text of the update query within a trigger?

As far as I know, there is not a way to obtain that information via
trigger. I would suggest trying to capture the information via the SQL
Profiler. Sorry I could not be of more assistance.

Regards,

Enrique Martinez
Sr. SQL Server Developer

--CELKO--

3/11/2007 12:13:00 AM

0

>> I am essentially recording changes made to a table for auditing purposes. <<

I hope not! The audit data has to be separated from the database
that you are auditing. You need to talk to the accounting department
and the lawyers before you commit any more crimes. Ask about SOX,
tell them what you want to do and watch their faces.

>> However, I created it on a table of a third party app over which I have no access to the code. One of the things I want to record is from which application window the update statement was issued. <<

You will need a third party or external audit tool that uses the log
file (which I hope is on a separate physical drive or tape).

>> Is there a way to reference the text of the update query [sic] within a trigger? <<

No. But do you understand that an UPDATE statement is totally unlike
a query?

Shuurai

3/13/2007 4:14:00 PM

0

On Mar 10, 8:13 pm, "--CELKO--" <jcelko...@earthlink.net> wrote:
> >> I am essentially recording changes made to a table for auditing purposes. <<
>
> I hope not! The audit data has to be separated from the database
> that you are auditing. You need to talk to the accounting department
> and the lawyers before you commit any more crimes. Ask about SOX,
> tell them what you want to do and watch their faces.

Joe,

For the love of God will you *please* do some actual research into
this before you post even one more asinine response to someone on this
subject. Because you are really talking out of your butt on this
one. And please don't bother giving me the list of committies you've
been on or the books you've written. It won't change anything.

First off, we have NO idea from his post what type of business he is
in or if SOX even applies. Second, we have NO idea from his post what
type of data he is storing or if SOX even applies. Third, even if SOX
does apply to his business and to his data, you need to get it through
your head that it is NOT illegal or even against SOX to have audit
data that is within your database. The requirement is that you DO
have audit data that is seperate, and in case you can't figure out for
yourself why those two statements are different: You can have audit
data in more than one place and for various reasons.

Telling someone that he is committing a crime via SOX when you have NO
idea whether or not his company is required to follow SOX or whether
SOX is applicable to his data is simply ignorant and irresponsible.

> >> However, I created it on a table of a third party app over which I have no access to the code. One of the things I want to record is from which application window the update statement was issued. <<
>
> You will need a third party or external audit tool that uses the log
> file (which I hope is on a separate physical drive or tape).

IF that is actually required for his data and his business - ie. if
SOX actually applies. By all means he should check with his legal
department and find out - you know, rather than make blind assumptions
or following the blind assumptions of some doofus on usenet.

mikeg

3/20/2007 3:09:00 PM

0

You may rest easy tonight, knowing that we are not a public company and are
not under SOX. Even so, I was thinking - a completely evil and unique
thought, I know - that I might like to have a clearer handle on what is
happening in our systems when discrepancies arise. While knowing that my
data is accurate is troublesome to some, such as yourself, I just can't help
myself...

Get a grip! ;-)

Anyway, thanks for the concern...

"--CELKO--" wrote:

> >> I am essentially recording changes made to a table for auditing purposes. <<
>
> I hope not! The audit data has to be separated from the database
> that you are auditing. You need to talk to the accounting department
> and the lawyers before you commit any more crimes. Ask about SOX,
> tell them what you want to do and watch their faces.
>
> >> However, I created it on a table of a third party app over which I have no access to the code. One of the things I want to record is from which application window the update statement was issued. <<
>
> You will need a third party or external audit tool that uses the log
> file (which I hope is on a separate physical drive or tape).
>
> >> Is there a way to reference the text of the update query [sic] within a trigger? <<
>
> No. But do you understand that an UPDATE statement is totally unlike
> a query?
>
>
>
>
>

mikeg

3/20/2007 3:53:00 PM

0

Oooh...I have to do one more comment, because this was such a funny post.

I understand the difference between the words "query" and "statement".
Apparently, you enjoy pointing out trivial errors in the writings of other
people. So, I feel obliged to point out errors in your grammar.

Did you realize that the following portion of your reply is a run-on sentence?
"Ask about SOX, tell them what you want to do and watch their faces." I
must point out that it should be split into TWO sentences, separated at the
comma. Additionally, you will need to supply a direct object in the first
sentence, as "Ask about SOX" does not clearly specify to whom I should be
referring the question. A predicate pronoun such as "them" will do nicely.

Also, the statement beginning with, "You will need...", should be altered to
say simply "You need..." By your phrasing, it would appear that you are
clairvoyant and are aware of what I will need in the future. As we are
clearly talking about the present, your superfluous use of the word "will" is
wholly incorrect.

Get my point?

"--CELKO--" wrote:

> >> I am essentially recording changes made to a table for auditing purposes. <<
>
> I hope not! The audit data has to be separated from the database
> that you are auditing. You need to talk to the accounting department
> and the lawyers before you commit any more crimes. Ask about SOX,
> tell them what you want to do and watch their faces.
>
> >> However, I created it on a table of a third party app over which I have no access to the code. One of the things I want to record is from which application window the update statement was issued. <<
>
> You will need a third party or external audit tool that uses the log
> file (which I hope is on a separate physical drive or tape).
>
> >> Is there a way to reference the text of the update query [sic] within a trigger? <<
>
> No. But do you understand that an UPDATE statement is totally unlike
> a query?
>
>
>
>
>