Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Amethystium
Aged Yak Warrior
701 Posts |
 Posted - 2003-08-08 : 10:00:50
|
| Hi all,I am in the process of developing a script that adds some records to 3 perhaps 4 tables to our software database. The script will eventually be adapted to loop through all databases on the selected server and carry out the updates. I've been thinking of creating a trigger that would insert a record into an audit trail table to track all the inserts but my worry is that the trigger might fire when (an Admin using the software, or a developer) decides to insert a record into the same table at the same time.Is there a way of avoiding this problem? Or should I revise my whole approach?The second question I have is this. The script has quite a lot of dynamic SQL which performs various inserts. In order to make the code more readable I have decided to separate my inserts into different sets of Dynamic SQL statements. For example the first @SQL variable would hold the string to insert into my first table. Re-initialise @SQL and build a new string etc. I know that Dynamic SQL is inefficient at best, but will this approach slow it down further?Thanks in advance.------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-08 : 10:15:23
|
| Are you talking about insertes or updates ... you mix the language.Triggers ON INSERT execute when data is INSERTed into a table, regardless of who the client is. What that particular trigger does is dependant on the DML compiled into the trigger. Maybe you could further explain your concern with an example.I think the multiple @SQL thing will incur a performance hit due to each inserts execution plan created seperately. I'd be surprised, however, if you could measure this performance hit, really ...Jay White{0} |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-08 : 10:23:39
|
| Page47,Sorry I will explain it better this time. I am only doing inserts and like I said the insert will probably affect 3-4 tables (not sure on this one yet), but I want to keep track of what I am inserting. The table I am inserting to gets quite a number of hits. (Quite a vital table in the database, well it's a history table so you know what I mean). I am worried that my trigger will also fire if another developer/Pension Admin decides to access/insert the table through the front end or QA. My main question is if there is a way to provide a locking mechanism when the trigger fires.Either that or maybe stick the whole insert in to a Tran.I don't know...------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-08 : 10:38:03
|
quote:
My main question is if there is a way to provide a locking mechanism when the trigger fires.
You should read up on locking architecture, isolation levels and blocking in Books Online. I think you'll find that SQL will handle this for you, unless you sprinkle nolocks all over your code like so many people ...Jay White{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-08 : 10:53:13
|
| Every sql statement is atomic i.e. runs as though inside a transaction. The trigger will automatically be part of that transaction.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Triggers and concurrency