Trigger And Transactions On Temporary Tables

can we create trigger and transactions on temporary tables? when user will insert data then , if it is committed then the trigger would be fired , and that data would go from the t

Solution 1:

I don't think you understand triggers - trigger firing is associated with the statement that they're related to, rather than when the transaction commits. Two scripts:

Script 1:

createtable T1 (
    ID intnotnull,
    Val1 varchar(10) notnull
)
go
createtable T2 (
    ID intnotnull,
    Val2 varchar(10) notnull
)
go
createtrigger T_T1_I
on T1
after insertasinsertinto T2 (ID,Val2) select ID,Val1 from inserted
go
begin transaction
insertinto T1 (ID,Val1)
select10,'abc'
go
RAISERROR('Run script 2 now',10,1) WITH NOWAIT
WAITFOR DELAY '00:01:00'
go
commit

Script 2:

select*from T2 with (nolock)

Open two connections to the same DB, put one script in each connection. Run script 1. When it displays the message "Run script 2 now", switch to the other connection. You'll see that you're able to select uncommitted data from T2, even though that data is inserted by the trigger. (This also implies that appropriate locks are being held on T2 by script 1 until the trigger commits).

---

Since this implies that the equivalent of what you're asking for is to just insert into the base table and hold your transaction open, you can do that.

If you want to hide the actual shape of the table from users, create a view and write triggers on that to update the base tables. As stated above though, as soon as you've performed a DML operation against the view, the triggers will have fired, and you'll be holding locks against the base table. Depending on the transaction isolation level of other connections, they may see your changes, or be blocked until the transaction commits.

Solution 2:

Triggers cannot be created on temp tables. But it is an unusual requirement to do so.

Temp tables can be part of a transaction, BUT table variables cannot.

As @Damien points out, triggers do NOT fire when a transaction is commited, rather they fire when an action on the table (INSERT, UPDATE, DELETE) with a corresponding trigger occurs.

Solution 3:

Or create a view that you can insert data into. It will write back to the table and then the triggers will fire.