CODEWITHSUNDEEP
sqlsql-serverdatabase
user10
user10

Reputation: 167

Create daily log table using triggers

I have a query, the results of which are stored in a table.

select id, name, category, date1, count1,count2, count3
into stage
from table1 t1 join table2 t2 on t1.is =t2.id join table3 t3 on t2.id = t3.id

The results of this query must be stored daily in a new log table with an additional date field added that captures the datetime it was logged. How do I create this?

Upvotes: 1

Views: 643

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

You can do it via a trigger but cannot recreate the table stage because every time you recreate it (with the into) you lose the trigger. Try this pattern:

create table t21 (i1 int) -- source table
create table t21s (i1 int) -- stage table
create table t2log(i1 int, when1 datetime); -- log table
go
;
create trigger t_t21s on t21s after insert
as
set nocount on
insert into t2log(i1, when1)
select inserted.i1,getdate() 
    from inserted;


    insert into t21 values (5)
    -- every day or whenever you want to fill the staging table
    truncate table t21s -- every day or period
    insert into t21s (i1) -- fill up stage table without destroying trigger

    select * from t21 -- see what is in stage

    select * from t2log -- see what is in log

Upvotes: 1

Related Questions