Trigger did not execute

Question

I wrote a simple trigger:

CREATE TRIGGER test_tr 
ON bi_test_tr
AFTER INSERT, UPDATE, DELETE
AS
BEGIN  
    INSERT INTO test (datechange, sum1, sum2, sum3) 
        SELECT 
            GETDATE(), SUM([filed1]), SUM([filed2]), SUM([filed3])
        FROM bi_test_tr 
END
GO

Trigger should insert sum of 3 columns (filed1, filed2, filed3) into table test with current date.

But when I upload new data set into bi_test_tr, nothing was inserted into test.

Do you know why?

Any SQL Server trigger that fails to reference either [inserted or deleted](https://msdn.microsoft.com/en-GB/library/ms191300.aspx) sets off warning bells - you're not, in your trigger, making any use of the information about *which* rows in the table has just been changed. — Damien_The_Unbeliever, May 12 '16 at 08:51
what is also strange: when I run code from trigger, it run perfect :/ — 4est, May 12 '16 at 10:49

score 1 · Accepted Answer · answered May 12 '16 at 09:04

1

I just tested your code with the following:

create table bi_test_tr (
    filed1 int,
    filed2 int,
    filed3 int
)
GO

create table test (
    datechange datetime,
    sum1 int, 
    sum2 int, 
    sum3 int
)
GO

CREATE TRIGGER test_tr 
ON bi_test_tr
AFTER INSERT, UPDATE, DELETE
AS
BEGIN  
    INSERT INTO test (datechange, sum1, sum2, sum3) 
        SELECT 
            GETDATE(), SUM([filed1]), SUM([filed2]), SUM([filed3])
        FROM bi_test_tr 
END
GO

insert into bi_test_tr values (1,1,1);
insert into bi_test_tr values (2,2,2);
insert into bi_test_tr values (3,3,3);
insert into bi_test_tr values (4,4,4);
insert into bi_test_tr values (5,5,5);

select * from test

And got the expected result:

2016-05-12 12:03:09.253    1    1   1
2016-05-12 12:03:09.287    3    3   3
2016-05-12 12:03:09.307    6    6   6
2016-05-12 12:03:09.307   10    10  10
2016-05-12 12:03:09.310   15    15  15

Unless I'm missing something?

answered May 12 '16 at 09:04

Radu Jakab

64
1
5

hi, your code is working....I don't know why what is wrong with my maybe my select? because into table bi_test_tr I had a col1,col2....col20, and I need to do other select? – 4est May 12 '16 at 09:38
hmm, doesn't seem like that should matter at all; look for simpler things, and double check - is the trigger really deployed? What if you do a PRINT statement within it? Try to take it step by step... – Radu Jakab May 12 '16 at 10:17
is deployed: I got exactly the same result as when I run your code.....how to PRINT it ?? – 4est May 12 '16 at 10:29
2

also Im using wizard import data to append it to table -> can it be reason? – 4est May 12 '16 at 10:30
1

@4est: YES! When you import data through bulk insert, triggers are not fired by default. https://technet.microsoft.com/en-us/library/ms187640(v=sql.105).aspx – Nenad Zivkovic May 12 '16 at 11:04

score 0 · Answer 2 · answered May 12 '16 at 08:46

I think you should do the select first and then the insert. But I'm not sure of that but you should do

select getdate(), sum([field1]), sum([field2]), sum([field3]) into var1, var2, var3;

And those 3 variables must be declared first. Finally you just have to insert those variables.

Again, I'm not sure of that, but you can try :)

Trigger did not execute

2 Answers2