trigger and transactions on temporary tables

Question

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 temporary table into the actual tables.

and when the SQL service would stop, or the server would be shutdown, then the temporary tables would be deleted automatically.

or shall i use an another actual table , in which first the data would be inserted and then if it is committed then the trigger would be fired and the data would be sent to the main tables and then i would execute a truncate query to remove data from the interface table, hence removing the duplicate data.

Answer 1

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.

Answer 2

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

Answer 3

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:

create table T1 (
    ID int not null,
    Val1 varchar(10) not null
)
go
create table T2 (
    ID int not null,
    Val2 varchar(10) not null
)
go
create trigger T_T1_I
on T1
after insert
as
    insert into T2 (ID,Val2) select ID,Val1 from inserted
go
begin transaction
insert into T1 (ID,Val1)
select 10,'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.