| Author |
Topic |
|
mariechristine
Starting Member
27 Posts |
 Posted - 2004-10-04 : 09:23:27
|
| i want to audit transactions done to table TOrig.I created table TAudit same as TOrig in addition to ActionID (1 for insert, 2 for update, 3 for delete), System Date and System User.I created triggers on TOrigto insert into TAudit in case of insert, update & delete.TOrig contains text column. So i created instead of Trigger like:Create TRIGGER TrigDeleteON dbo.TOrigInstead of DeleteAS declare @id int select @id=Liq_ID from Deleted If Not Exists (Select * from TAudit where Liq_ID=@id and ActionID=3 ) BEGIN Insert into TAudit select * , 3, GetDate(), System_USer from Deleted Delete from TOrig where Liq_ID=@id ENDGOPROBLEM: is that Torig is a detail table to a master table and has a cascade delete relationship with the master table.So Instead Trigger does it work. What do I DO???? |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-10-04 : 10:28:38
|
| Weel there are a couple of problems...First being that your treating the trigger like it will only process 1 row at a time.Do you have an Oracle background?You have to allow for a set of rows at a time.And you not use U and D....instead of numbers....like something that you have to remeber....And why worry about Inserts? They will be in the base table.Brett8-) |
 |
|
|
mariechristine
Starting Member
27 Posts |
Posted - 2004-10-05 : 03:04:18
|
| I would've liked to use For Delete trigger but i can't since there are text columns in the table. And i can't retrieve rows from Deleted table when there are text columns. That's why i used Instead of Delte trigger. But now i'm hung cause Instead of Delete trigger doesn't work with Cascade. That's why i'm confused now. There are 2 problems: text columns in the table, and cascade delete. How do i solve the 2 problems? |
|
|
|
mariechristine
Starting Member
27 Posts |
Posted - 2004-10-05 : 10:50:38
|
| So what's the solution for the 2 problems?? |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-10-05 : 15:02:02
|
| TRY SETTING THIS IN THE PROPERTIES OF THE TABLE IN THE EM TABLE DESIGN VIEW*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
Weevil
Starting Member
5 Posts |
Posted - 2004-10-05 : 16:45:17
|
| In the delete trigger, you should be able to pull the id's from the row being deleted, then query the TOrig table to get the text values and insert them into your audit tables. The rows are still out there until your trigger finishes, so you can still access them before they go away. Don't use the instead of delete trigger (this is mainly useful when you want to update a multi-table view).Do your inserts into the audit table by joining deleted against TOrig, then let the deletes finish. |
|
|
|
mariechristine
Starting Member
27 Posts |
Posted - 2004-10-06 : 10:25:12
|
| Joining Deleted with Torig table inside a FOR DELETE trigger and then retrieving the rows from torig result in nothing. No rows are inserted in TAUdit cause the row in Torig is deleted already, it doesn't hold values until trigger finishes. |
|
|
|
Weevil
Starting Member
5 Posts |
Posted - 2004-10-06 : 14:47:16
|
| You're right. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table, but it won't work with delete triggers. Sorry.You can use an AFTER DELETE trigger to do your auditing after all of the constraint checks and cascades have completed.As far as your text column goes, it looks like you're stuck. What is being stored here? Is it so big that it really needs a text column, or would a varchar(8000) be big enough? Otherwise it seems like a lot of data to be tracking changes on.The other option would be to turn your logic around and do your auditing on insert and update. If you snapshot the record at creation and then again on every update, then just log a deletion notice in the audit table when it is removed, you'll have the history (including text columns) that you want and bypass this restriction, if I'm not mistaken. |
|
|
|
|
Help trigger