Not sure if anyone in here knows the answer to this, but I asked in a SQL
group and haven't had a suitable answer and since the front end app is ASP I
though I'd give here a try.
I am trying to create a simple trigger in my SQL DB so that when a record is
updated or deleted a copy of the original record is placed in an audit
table.
However, I keep getting the following error:
Server: Msg 311, Level 16, State 1, Procedure SYS_Individual_ AUDIT_Trigger,
Line 9
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.
Now apparently this is because the TEXT column I have cannot be 'logged' or
so someone told me.
Is there any way of using a trigger (an idiot proof way) so that I can copy
a record to an audit table when it is updated/deleted if the record contains
one or more TEXT columns?
If all else fails I will write it into my app but I am trying to avoid that
if possible.
Thanks 3 3475
Keith wrote: Not sure if anyone in here knows the answer to this, but I asked in a SQL group and haven't had a suitable answer and since the front end app is ASP I though I'd give here a try.
I am trying to create a simple trigger in my SQL DB so that when a record is updated or deleted a copy of the original record is placed in an audit table.
However, I keep getting the following error:
Server: Msg 311, Level 16, State 1, Procedure SYS_Individual_ AUDIT_Trigger, Line 9 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Now apparently this is because the TEXT column I have cannot be 'logged' or so someone told me.
Is there any way of using a trigger (an idiot proof way) so that I can copy a record to an audit table when it is updated/deleted if the record contains one or more TEXT columns?
If all else fails I will write it into my app but I am trying to avoid that if possible.
Thanks
If you are using SQL 2000, you can use an "Instead Of" trigger. Normal
trigger fire after the triggering action occurs, so the only place to find
the old data is in the deleted table. Instead Of triggers fire before the
triggering action is performed, allowing you to perform some activity
instead of the activity that would have been performed by the triggering
action. look it up in SQL BOL (SQL Books Online) and post any follow-up
questions you have to m.p.sqlserver.p rogramming.
If pre-SQL7, then you have no recourse but to perform all updates and
deletions via stored procedures which copy the original data to the audit
table before performing the intended action.
HTH,
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Would copying the first 8000 characters be enough? Do you really want to
store a copy of the entire row?
I suppose if you had a replica table, and the object table had a primary
key, you could do something like this in an instead of trigger:
INSERT tableCopy SELECT col1, col2, col3 FROM objectTable WHERE pk IN
(SELECT pk FROM inserted)
But man, that performance would not be good. And, you would have to handle
the normal operation as well (since the instead of trigger stops it from
happening).
-- http://www.aspfaq.com/
(Reverse address to reply.)
"Keith" <@.> wrote in message news:O3******** ******@TK2MSFTN GP10.phx.gbl... Not sure if anyone in here knows the answer to this, but I asked in a SQL group and haven't had a suitable answer and since the front end app is ASP
I though I'd give here a try.
I am trying to create a simple trigger in my SQL DB so that when a record
is updated or deleted a copy of the original record is placed in an audit table.
However, I keep getting the following error:
Server: Msg 311, Level 16, State 1, Procedure
SYS_Individual_ AUDIT_Trigger, Line 9 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Now apparently this is because the TEXT column I have cannot be 'logged'
or so someone told me.
Is there any way of using a trigger (an idiot proof way) so that I can
copy a record to an audit table when it is updated/deleted if the record
contains one or more TEXT columns?
If all else fails I will write it into my app but I am trying to avoid
that if possible.
Thanks
Need to capture teh entire record as the data in it will be legally binding
and the slightest change could be crucial.
Performance is not too much of an issue as it will not be heavy on useage,
but the useage there is needs auditing.
"Aaron [SQL Server MVP]" <te*****@dnartr eb.noraa> wrote in message
news:eh******** ******@TK2MSFTN GP10.phx.gbl... Would copying the first 8000 characters be enough? Do you really want to store a copy of the entire row?
I suppose if you had a replica table, and the object table had a primary key, you could do something like this in an instead of trigger:
INSERT tableCopy SELECT col1, col2, col3 FROM objectTable WHERE pk IN (SELECT pk FROM inserted)
But man, that performance would not be good. And, you would have to
handle the normal operation as well (since the instead of trigger stops it from happening).
-- http://www.aspfaq.com/ (Reverse address to reply.)
"Keith" <@.> wrote in message
news:O3******** ******@TK2MSFTN GP10.phx.gbl... Not sure if anyone in here knows the answer to this, but I asked in a
SQL group and haven't had a suitable answer and since the front end app is
ASP I though I'd give here a try.
I am trying to create a simple trigger in my SQL DB so that when a
record is updated or deleted a copy of the original record is placed in an audit table.
However, I keep getting the following error:
Server: Msg 311, Level 16, State 1, Procedure SYS_Individual_ AUDIT_Trigger, Line 9 Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.
Now apparently this is because the TEXT column I have cannot be 'logged' or so someone told me.
Is there any way of using a trigger (an idiot proof way) so that I can copy a record to an audit table when it is updated/deleted if the record contains one or more TEXT columns?
If all else fails I will write it into my app but I am trying to avoid that if possible.
Thanks
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: cxw0106 |
last post by:
Is there any way to implement File Auditing in .NET?
Thanks.
|
by: John Pan |
last post by:
Hi
I am looking to implement an audit/history table/tables but am looking
at doing this without the use of triggers.
The reason for doing this is that the application is highly
transactional and speed in critical areas is important.
I am worried that triggers would slow things down.
I am more used to other database where by there is a utility to "dump"
|
by: BUSHII |
last post by:
I have little problem and I dont have any idea how to make my trigger.
I have table MyTable where I have many column with almost same name and same
type (Grp1,Grp2,Grp3,Grp4...Grp50 char(1)).
I`d like to make some trigger on UPDATE this table. I must to check which
column was changed.
For example to check if Grp1 was changed I can try this:
ALTER TRIGGER MyTrigger
|
by: Zlatko Matiæ |
last post by:
Hello.
I tried to implement audit trail, by making an audit trail table with the
following fileds:
TableName,FieldName,OldValue,NewValue,UpdateDate,type,UserName.
Triggers on each table were set to do the job and everything was fine except
that in the audit trail you couldn't know which row exacltly was
updated/inserted/deleted...Therefore I introduced 3 additional columnes
(RowMark1, RowMark2, RowMark3) which should identify the...
|
by: Paulo Jan |
last post by:
Hi all:
Let's say I'm designing a database (Postgres 7.3) with a list of all
email accounts in a certain server:
CREATE TABLE emails (
clienteid INT4,
direccion VARCHAR(512) PRIMARY KEY,
login varchar(128) NOT NULL,
| |
by: RdR |
last post by:
Is it true that DB2 will have an auditing tool to be used for auditing
requirements such as Sarbanes-Oxley, etc? Something called Websphere
Compliance Auditing? Rumours have it that IBM has a new tool that will get
useful data for auditing from the DB2 logs through a log analyzer or log
scraping mechanism and feed it to a SQL based database Target, or a Q
replication target or use Web Publishing. Any dates announced?
Thanks,
RdR
|
by: Ed Rauscher |
last post by:
Does ayone know what Class is used to enable Registry Auditing? Any help
would be great.
|
by: Jonas |
last post by:
Hi!
I'm developing the middletiers of an ASP.NET application in VB.NET. I've got
a business logic layer in which I would like to perform auditing to a
database. Instead of making an auditing call in every method of my classes,
would it be a workable way to implement IDisposable in the base class to all
the BLL-classes and then in the Dispose method to do the audit call? Do I
then have to make sure that all uses of the BLL-classes end...
|
by: Rico |
last post by:
Hello,
I'm creating an audit table and associated triggers to be able to capture
any updates and deletes from various tables in the database. I know how to
capture the records that have been updated or deleted, but is there any way
that I can cycle through a changed record, look at the old vs new values and
capture only the values that have changed?
To give you a better idea of what I'm trying to do, instead of creating a
copy of the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |