|
I have written some triggers that fire when data is updated/inserted/deleted. These triggers write entries to the Audit tables.
I want to include the username of the person who made the change. I started by using SUSER_SNAME which works great on the surface.
The trouble is that our system is n tier. There is a business logic server that does all of the access to the database. users access the system via a client app which communicates with the BLL server which writes to the database. Users never communicate with the database directly. This means that the audit logs all have "serveruser" in them, which is the user the server runs under.
Is there any way I can get the audit logs to reflect the user that told the server to make the change, rather than the user that the server is running under.
We don't use stored procedures (we are using a combination of the entity framework and parametrized queries) so we can't just pass the user name to them and let them do it.
(Perhaps is there something to do with impersonation that would do this? I don't want to grant access to the database to any normal users though.)
(I'm using SQL server 2000 and .net 3.5)
Any ideas?
[Edit: I have a solution.
Include a LastModifiedBy field on the table. This value is then set by the server to match the user that made the call to the server. This field is then accessible from the trigger so can be set into the audit table.
What do people think of that solution? Does it sound sensible? Are there any pitfalls of this I should be aware of?]
Simon
modified on Tuesday, March 24, 2009 8:44 AM
|
|
|
|
|
The only effective way I've ever been able to do this is to pass the identity down through the tiers.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys
|
|
|
|
|
Thanks for replying so quickly Pete.
Pete O'Hanlon wrote: The only effective way I've ever been able to do this is to pass the identity down through the tiers.
Yeah, we do currently do that. We have a session object that contains a bunch of user related stuff for each call to the server, and that is passed down through each tier (it's used to validate that the user has permission to do what they are requesting, and to filter returned results to only stuff that the user is allowed to see), but how do I get the the SQL server triggers to use that user name. We don't use stored procs so I can't just pass the user name into the stored proc.
Simon
|
|
|
|
|
I am working on a database project using oracle 10g database and asp.net. On the page, i have configured crystalreportviewer,crystalreportsource and a SQLDataSource for crystalreportsource. While configuring SQLDatasource it is asking me the input paramter but i do'nt know how can i pass sys_refcursor parameter from design/configuring time. Here is my code of stored procedure:
create or replace procedure get_emp(emn in integer,rc out sys_refcursor)
is
begin
open rc for select * from emp where empno=emn;
end;
/
This is working fine in SQLPlus as :
VARIABLE cv REFCURSOR
EXEC GET_EMP(7788,:CV);
print cv;
and it is working fine. I am not able to pass the "CV" from code/design time.
Please guide and help me.
Thanks and Regards
Girish Sharma
|
|
|
|
|
I have created a table with "bytea" datatype column. (I have not used oid datatype intentionally as its a pointer) I am using Windows XP machine. How can I store an image here?
Like in MySQL, actual image can be stored in a table with BLOB or Medium BLOB datatype.
|
|
|
|
|
Hi I Have Some Oracle question
1. How to activate Oracle Server and How to Close ?
2. How to connect to database ?
thank's
|
|
|
|
|
You asked this a couple of days ago and didn't get any answers - probably because your questions don't make any sense.
1. What do you mean by 'activating' and 'closing' Oracle Server?
2. Connect from what? A program, or a utility such as sqlplus?
Scott
|
|
|
|
|
I come from the SQL server world, and I know that I can open the SQL server
(the play icon near the clock)
how can i do it in Oracle ?
|
|
|
|
|
That sounds like the SQL Server Service Manager. I believe all it does is allow you to start/stop SQL Server related services. The method to do the same in Oracle may depend on the version and edition that you have installed. What are they?
I'm not in front of a computer with Oracle installed right now, and I can't remember off the top of my head how to do it in Oracle, but I think I used to do it by opening the services mmc plug-in and manually starting the Oracle processes I wanted running (Oracle itself, the listener, etc.). Also, see this page - http://download.oracle.com/docs/html/B10163_01/admin.htm[^]
Scott
|
|
|
|
|
what is mean by transaction in database? is "select" command is also included in transaction?
|
|
|
|
|
check this out ...
[^]
Yes, you could make a select statement part of a transaction. There are implications as to whether or not you would want to do such a thing because there may be negative implications depending on how your isolation level is set. Google "isolation level" for more detail.
In MS-SQL, you typically explicitly define a transaction something like this:
BEGIN TRANSACTION
<some sql="" etc="">
COMMIT
(or ROLLBACK)
|
|
|
|
|
if select command is use as single
like
select * from employee
then it 'll also included in transaction's defination ? how can i roll back this command?
|
|
|
|
|
Here is an example:
BEGIN TRANSACTION
select * from employee
ROLLBACK
This is syntactilly correct, but logically not very valid. A SELECT statement does not change the database, therefore a commit or rollback really does nothing except end the transaction. The above example should really should never be used, by starting a transaction you could be creating unnecessary locks to be granted.
Transactions are typically used to guarantee that ALL updates are made or NO updates are made.
For example: (syntax is not 100% accurate, but you should get the idea)
BEGIN TRANSACTION
try
UPDATE table1 set newbalance = newbalance - 100 where ID=ABC
INSERT into audittable values (NOW(),'ABC','WITHDRAWL')
COMMIT
catch ex
ROLLBACK
end try
This example shows that 2 tables are being updated and if they are successful, the transaction is committed, if there is an error then a rollback is performed.
|
|
|
|
|
Hi dear,
This is my first post and want help from some one.
My question is below:
I have five tables with fields named below:
- purchase (*purchase_id, purchase_date, company_id)
- purchase-detail (purchase_id, qty, product_id, unit_id)
- sale (*sale_id, sale_date)
- sale-detail (sale_id, qty, company_id, product_id, unit_id))
Note - * represents primary key
(company_id, product_id, unit_id) makes one item.
I am creating a crystal report using vb.net 2008 and I have to create specific Item detail, in which date it is sold out , in which date it is purchased.
I have created crystal report 4 columns below in which data will be placed
Date(sale/purchase) | Purchase Quantity | Sale Quantity | Rem. Quantity
23/03/09 | 300 | | 300
24/03/09 | | 100 | 200
25/03/09 | 100 | 200 | 100
The above format will be used with different possibilities like above.
I have different and same dates in sale and purchase tables with different quantities for same date .
Please help me to solve this question . I am unable to create the queries.
Many thanks in Advance
modified on Monday, March 23, 2009 8:53 AM
|
|
|
|
|
Hi,
I've used an SqlDataReader to retrieve some rows from my database. I can then iterate through the results calling its Read() method but how can I determine the number of rows without having to call the Read() method?
Thanks 
|
|
|
|
|
Do a SELECT COUNT(*) that you read before entering the loop, or use a DataSet (with DataAdapter.Fill).
I are troll
|
|
|
|
|
Thanks for your reply
I'm a bit of a SQL noob so I'll have to look into using a DataSet but doing a SELECT COUNT will be effectively calling the same query twice (apart from actually returning the columns). Won't that be a great deal of overhead?
I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
|
|
|
|
|
Member 6038196 wrote: Won't that be a great deal of overhead?
A great deal? Not unless you're going to call this query often, couting the rows in a table and returning the result is quite fast.
Member 6038196 wrote: I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
If memory serves correct, it didn't - it just keeps getting records until it runs out of records. If you want both the records and the count in a single SQL-statement, use the DataAdapter.Fill method
I are troll
|
|
|
|
|
Thanks - much appreciated
|
|
|
|
|
Member 6038196 wrote: I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?
A datareader only ever knows about 1 row. It's what's known as a Firehose cursor, whereby data is squirted through as quickly as possible. You could always just increment a count on each read and use that.
"WPF has many lovers. It's a veritable porn star!" - Josh Smith As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.
My blog | My articles | MoXAML PowerToys
|
|
|
|
|
Another solution for you if the others do not work,
SqlDataReader reader = cmd.ExcecuteReader();
int count = 0;
while ( reader.read){
// do your normal code for gathering column info
count++;
}
when its done reading you'll have your data and your count.
Hope that helps.
|
|
|
|
|
I am trying to select data from tables A and B
SELECT A.* , B.*
FROM A INNER JOIN B ON A.Id = B.Id
WHERE (A.Id=@id) AND(A.Date BETWEEN B.Date1 AND B.Date2)
the problem appears that fields B.Date1 and B.Date2 are not necessary containing data,
they can be null. so, my solution is
WHERE (A.Id=@id) AND
(A.Date BETWEEN CASE B.Date1 WHEN null THEN @DateMin ELSE B.Date1 END
AND CASE B.Date2 WHEN null THEN @DateMax ELSE B.Date2 END)
when i run this select i get error:
"The version of sql server in use does not support datatype 'udt'"
i can't figure out why i get this error. is there something to do with
@DateMin and @DateMax format ?? i think i am using the right format
can anybody help me,
thanks
|
|
|
|
|
As far as I know, there is no such ptredefined variables as @DateMin and @DateMax - assuming you are using sql server.
Something like this should get you what you want
WHERE (A.Id=@id) AND
(A.Date BETWEEN coalesce(B.Date1,A.Date) AND coalesce(B.Date2,A.Date))
The coalesce means take the first non-null value
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
thanks or your answer
i have this problem sorted out with ISNULL()
WHERE (A.Id=@id) AND
(A.Date BETWEEN ISNULL(B.Date1,@DateMin) AND coalesce(B.Date2,@DateMax))
@DateMin = '01/01/1900'
@DateMax = '01/01/2079'
works perfectly. why i couldn't come out with this at first
|
|
|
|
|
Just for info, coalesce is the ansi standard and has the advantage of allowing more than 2 options eg
coalesce(val1,val2,val3, val4) etc wll take the first non-null value rather than just isnull(val1,val2)
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
