I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)
See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)
|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury
|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Monday, March 19, 2012
Auditing: some useful (?) code and a question
I'm still trying to decide the "right" solution for auditing, but here's one
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
solution I've been looking at. It uses dynamic SQL in order to be able to
handle changes to the underlying tables without changes. There's also some
code (not included) that sets up the auditing table based on the underlying
table.
My question: I notice this results in lots of "updates" where nothing
changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
statement", which is not the same thing at all!
Any suggestions on the best way to say "insert this record, but only if
something really changed"?
-- start off the SQL
select @.sql = 'insert mauryaudit (transactionType, transactionUser,
transactionDate' + CHAR(13) + CHAR(10)
-- add the field list
select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close the field list and start the select statement
select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
@.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
-- continue the select statement
select @.field = 0
while @.field < @.maxfield
begin
select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
end
-- close it off
select @.sql = @.sql + ' from #ins i'
exec (@.sql)See
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.nigelrivett.net/GenerateTriggerForAudit.html
And probably all the articles unser triggers on www.nigelrivett.net.
To only log changes you will have to check the before and after values of
each field.
Not that you will have problems if there is a blob in the table.
http://www.nigelrivett.net/Columns_updated.html
And has bigger problems if the column order has moved due to an alter table.
columns_updated also gets a bit awkward for more than 32 columns
"Maury Markowitz" wrote:
> I'm still trying to decide the "right" solution for auditing, but here's one
> solution I've been looking at. It uses dynamic SQL in order to be able to
> handle changes to the underlying tables without changes. There's also some
> code (not included) that sets up the auditing table based on the underlying
> table.
> My question: I notice this results in lots of "updates" where nothing
> changes. I noticed that COLUMNS_UPDATED is _really_ "columns in the UPDATE
> statement", which is not the same thing at all!
> Any suggestions on the best way to say "insert this record, but only if
> something really changed"?
> -- start off the SQL
> select @.sql = 'insert mauryaudit (transactionType, transactionUser,
> transactionDate' + CHAR(13) + CHAR(10)
> -- add the field list
> select @.field = 0, @.maxfield = max(ORDINAL_POSITION) from
> INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'test'
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close the field list and start the select statement
> select @.sql = @.sql + ')' + CHAR(13) + CHAR(10) + 'select ''U'', ''' +
> @.NowUser + ''', ''' + CAST(@.NowDate as varchar) + '''' + CHAR(13) + CHAR(10)
> -- continue the select statement
> select @.field = 0
> while @.field < @.maxfield
> begin
> select @.field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS
> where TABLE_NAME = 'test' and ORDINAL_POSITION > @.field
> select @.fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where
> TABLE_NAME = 'test' and ORDINAL_POSITION = @.field
> select @.sql = @.sql + ',i.' + @.fieldname + CHAR(13) + CHAR(10)
> end
> -- close it off
> select @.sql = @.sql + ' from #ins i'
> exec (@.sql)|||"Nigel Rivett" wrote:
> See
> http://www.nigelrivett.net/AuditTrailTrigger.html
> http://www.nigelrivett.net/GenerateTriggerForAudit.html
If I am reading them correctly, the "trick" is to join on a per-column
basis, thereby generating no inserts for those fields that do not have an
actual change. However it is not so clear how to do this in my trigger, where
the entire change "set" is entered as a single row instead of looping.
I could add another loop to check to see if any columns actually changed
(comparing inserted and deleted as you do), but it would see that it would be
much more effeceint to do this in a single SQL statement. I could write a
loop/exec to do that, but I'm curious if you could suggest a better way to
handle that? Is there some SQL that will compare one entire row with another
without listing every column?
Maury|||"Nigel Rivett" wrote:
I tried the obvious and expanded the loop to build three portions of the
insert...
1) the INSERT portion with the field list
2) the SELECT portion taking the values from inserted
3) the WHERE section comparing inserted and deleted
Sadly the result is >8000 chars, and fails. Is there some way to make the
statement significantly shorter? The checks for before/after nulls takes up
most of the room, is there some easier way to do this with the equality
check? Perhaps using ANSI/non-ANSI nulls?
Thursday, March 8, 2012
Audit table
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
MichalChapter 6: Audit Logging in the book "Transact-SQL Cookbook" discuses audit
logging using triggers in detail:
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
Michal|||Hi,
Copy the Order table structure to AuditOrder table.
Select * into AuditOrders from Orders where 1= 2
Auditing trigger:-
--
CREATE TRIGGER Audittriiger_order ON Order
FOR INSERT, UPDATE, DELETE
AS
if @.@.rowcount = 0 return
DECLARE @.action char (7), @.inserted bit, @.deleted bit
set @.deleted = case when exists (select * from deleted) then 1 else 0 end
set @.inserted = case when exists (select * from inserted) then 1 else 0 end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'Before'
else 'DELETE'
end
if @.deleted = 1
begin
INSERT INTO AuditOrders ( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'After'
else 'INSERT'
end
if @.inserted = 1
begin
INSERT INTO AuditOrders( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
end
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
> Hi All
> I'm looking for some code which create audit table and build triggers
> which register all changes on table.
> Maybe someone from you know where can I find it?
> Regards
> Michal
>|||Michael may also want to have an additional date/time column in AuditOrder
that stores when the change took place (getdate) and perhaps another column
to store the system username (system_user) of who initiated the change.
"Hari Pra
" <hari_pra_k@.hotmail.com> wrote in message
news:e5ZpZ5tsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Copy the Order table structure to AuditOrder table.
> Select * into AuditOrders from Orders where 1= 2
> Auditing trigger:-
> --
> CREATE TRIGGER Audittriiger_order ON Order
> FOR INSERT, UPDATE, DELETE
> AS
> if @.@.rowcount = 0 return
> DECLARE @.action char (7), @.inserted bit, @.deleted bit
> set @.deleted = case when exists (select * from deleted) then 1 else 0 end
> set @.inserted = case when exists (select * from inserted) then 1 else 0
> end
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'Before'
> else 'DELETE'
> end
> if @.deleted = 1
> begin
> INSERT INTO AuditOrders ( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
>
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'After'
> else 'INSERT'
> end
> if @.inserted = 1
> begin
> INSERT INTO AuditOrders( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
> end
>
> "Michal" <michalb77@.gazeta.pl> wrote in message
> news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
>
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
MichalChapter 6: Audit Logging in the book "Transact-SQL Cookbook" discuses audit
logging using triggers in detail:
http://vyaskn.tripod.com/transact-sql_cookbook.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
Hi All
I'm looking for some code which create audit table and build triggers which
register all changes on table.
Maybe someone from you know where can I find it?
Regards
Michal|||Hi,
Copy the Order table structure to AuditOrder table.
Select * into AuditOrders from Orders where 1= 2
Auditing trigger:-
--
CREATE TRIGGER Audittriiger_order ON Order
FOR INSERT, UPDATE, DELETE
AS
if @.@.rowcount = 0 return
DECLARE @.action char (7), @.inserted bit, @.deleted bit
set @.deleted = case when exists (select * from deleted) then 1 else 0 end
set @.inserted = case when exists (select * from inserted) then 1 else 0 end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'Before'
else 'DELETE'
end
if @.deleted = 1
begin
INSERT INTO AuditOrders ( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
set @.action = case when @.deleted = 1 and @.inserted = 1
then 'After'
else 'INSERT'
end
if @.inserted = 1
begin
INSERT INTO AuditOrders( operation, orderid, Order_name,
Order_qty,Order_date)
SELECT @.action AS F1,
RTRIM(SYSTEM_USER) AS F2,
[Order].Orderid,
[Order].Order_ name
FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
end
"Michal" <michalb77@.gazeta.pl> wrote in message
news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
> Hi All
> I'm looking for some code which create audit table and build triggers
> which register all changes on table.
> Maybe someone from you know where can I find it?
> Regards
> Michal
>|||Michael may also want to have an additional date/time column in AuditOrder
that stores when the change took place (getdate) and perhaps another column
to store the system username (system_user) of who initiated the change.
"Hari Pra
" <hari_pra_k@.hotmail.com> wrote in messagenews:e5ZpZ5tsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Copy the Order table structure to AuditOrder table.
> Select * into AuditOrders from Orders where 1= 2
> Auditing trigger:-
> --
> CREATE TRIGGER Audittriiger_order ON Order
> FOR INSERT, UPDATE, DELETE
> AS
> if @.@.rowcount = 0 return
> DECLARE @.action char (7), @.inserted bit, @.deleted bit
> set @.deleted = case when exists (select * from deleted) then 1 else 0 end
> set @.inserted = case when exists (select * from inserted) then 1 else 0
> end
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'Before'
> else 'DELETE'
> end
> if @.deleted = 1
> begin
> INSERT INTO AuditOrders ( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Deleted INNER JOIN Order ON Deleted.Orderid = Order.OrderID end
>
> set @.action = case when @.deleted = 1 and @.inserted = 1
> then 'After'
> else 'INSERT'
> end
> if @.inserted = 1
> begin
> INSERT INTO AuditOrders( operation, orderid, Order_name,
> Order_qty,Order_date)
> SELECT @.action AS F1,
> RTRIM(SYSTEM_USER) AS F2,
> [Order].Orderid,
> [Order].Order_ name
> FROM Inserted INNER JOIN Order ON Inserted.Orderid = Order.OrderID
> end
>
> "Michal" <michalb77@.gazeta.pl> wrote in message
> news:dfk13j$5hk$1@.atlantis.news.tpi.pl...
>
Friday, February 24, 2012
Attempting to Connect to SQLServer 2k
This is the code I'm using on the web page located on the server:
Dim strConn as String
Dim conn as SQLConnection
strConn="server=localhost;Trusted_Connection=Yes;database=CIS"
conn=New SQLConnection(strConn)
conn.Open()
and the error messgae I receive is: Login failed for user 'MSP00427\ASPNET'.
msp00427 is the name of the server
= = = = = = = = =
I have an MSAccess application that accesses the same database as follows with no trouble.
glblConnectString = "Driver=SQL Server;Server=msp00427;Database=CIS;Trusted_Connection=Yes"
Set cnn = New ADODB.Connection
cnn.Open glblConnectString
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "Scorecard_Delete_ATLASDataDump"
cmd.CommandType = adCmdStoredProc
cmd.Execute
This is run on my PC and I have sa capabilities on the server.
The .NET framework is installed.
Any ideas why the data access on the web page fails?you need to add the user 'MSP00427\ASPNET' to the db and giv ehim permissions. if yo xpand the databases tab on your sql server xplorer and then the database you are trying to connect to, you would see a Users tab. right click and add new user..
hth|||In SQL Server Enterprise Manager (or using scripts) you must add the 'MSP00427\ASPNET' windows user as a SQL Server user.
When you access an ASP.NET app, it is not YOUR rights that count, but the rights of the ASPNET user (by default).|||Thanks to those who responded so quickly.
I had just figured it out and tested it.
I was now coming back to post the solution for others.
You beat me to it.
Sunday, February 12, 2012
attaching a database to sql express
I'm trying to attach a database to sql express with the following code from the management tool. I keep getting an error message. An someone explain what I'm doing wrong.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '<'.
IF NOT EXISTS(
SELECT *
FROM sys.databases
WHERE name = N'<northwind.mdb, I4V0Y6\SQLEXPRESS, northwind>'
)
CREATE DATABASE <database_name, sysname, your_database_name>
ON PRIMARY (FILENAME = '<c:\my documents\my webs\myweb3,,C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF>')
FOR ATTACH
GO
Ira
You have to substitute the value in the <> signs with your actual values. SO it should evaluate to something like:
CREATE DATABASE Archive
ON (FILENAME = 'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF')
FOR ATTACH
The BOL has more samples about that.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)