April 24, 2012 at 11:13 am
I know couple of tools that generate this kind of temporary tables and the logic inside the tool is not sound enough to drop tables after use. It piles up unwanted tables in database. One good thing is the tool follows a naming conventions for the tables with which we can identify the table created by tool (not the application tables).
Create a dynamic query (or cursor, I don’t prefer) to generate drop statements and execute. It works well for me because I don’t want to log or preserve any information from these tables.
I am really not able to catch the idea of trigger and cursor (together) here.
April 25, 2012 at 3:28 am
thanks for the advice guys.
just to clear up the cursor/trigger issue (:blush:) it was along these lines, fairly standard i thought:
[font="Times New Roman"]SET NOCOUNT ON
DECLARE c_item CURSOR FAST_FORWARD FOR SELECT item_id FROM DELETED
DECLARE @abc_item_idINT,
@vs_table_name VARCHAR(30),
@vs_sql_stmt VARCHAR(255),
@vi_error INT,
@vi_output INT,
@vs_result_str VARCHAR(255),
@vs_msg_str VARCHAR(255)
OPEN c_item
FETCH NEXT FROM c_item INTO @abc_item_id[/font]
April 25, 2012 at 7:22 am
rarara (4/25/2012)
thanks for the advice guys.just to clear up the cursor/trigger issue (:blush:) it was along these lines, fairly standard i thought:
[font="Times New Roman"]SET NOCOUNT ON
DECLARE c_item CURSOR FAST_FORWARD FOR SELECT item_id FROM DELETED
DECLARE @abc_item_idINT,
@vs_table_name VARCHAR(30),
@vs_sql_stmt VARCHAR(255),
@vi_error INT,
@vi_output INT,
@vs_result_str VARCHAR(255),
@vs_msg_str VARCHAR(255)
OPEN c_item
FETCH NEXT FROM c_item INTO @abc_item_id[/font]
It's not standard practice, or least not widely recognized best practice, to place cursors within a table trigger. Even worse is DML statements (INSERT/UPDATE) within a trigger. However, this is the first time I've ever heard of the placement of DROP TABLE statements within a trigger; you're way out there on a limb with that one.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 25, 2012 at 8:11 am
sounds like we are pioneers! 😎
so what is the standard method for having the trigger fire multiple rows if I ran 'delete from table_123 where item_1 between 5 and 55'?
April 26, 2012 at 4:55 pm
Dj463 (4/19/2012)
As Deleting rows from tables in database would be logged onto transactional log if it's in full recovery mode, i've specified to change the recovery mode to simple. Once the data is deleted we could revert it back to full recovery mode.
a little off topic, but i feel this needs to be addressed...
the delete is logged regardless of what recovery mode the DB is in... the main benefit you get from simple here is that a checkpoint/log truncation is automatic and the log isn't waiting for you to take a backup in order to truncate.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply