SQL Server insert trigger not working

Question

This is my first time using triggers.

My trigger is not being triggered please help.

CREATE TRIGGER sbhack_autoban
ON LOG_CONNECT201211
FOR INSERT
AS
  BEGIN
      /* query to run if single or multiple data is 
       inserted into LOG_CONNECT201211 table */
      UPDATE login.dbo.USER_CHECK_LOGIN
      SET    login.dbo.USER_CHECK_LOGIN.CHECKLOGIN = 2
      WHERE  login.dbo.USER_CHECK_LOGIN.USER_KEY IN 
              (SELECT e.USER_KEY
                 FROM   game.dbo.CHAR_DATA0 AS e
                        INNER JOIN gamelogs.dbo.LOG_USING_DEPOT201211 AS p
                          ON e.CHAR_KEY = p.CHAR_KEY
                 WHERE  p.GATENUM = 150)
             AND login.dbo.USER_CHECK_LOGIN.CHECKLOGIN = 0
             AND login.dbo.USER_CHECK_LOGIN.USER_KEY != 51;
  END

This is suppose to run the query inside the BEGIN : END if an entry is inserted into the LOG_CONNECT201211 table. But nothing is happening even when I have inserted multiple data into LOG_CONNECT201211.

Your trigger isn't referencing INSERTED is that intentional? — Martin Smith
– Martin Smith, Commented Nov 11, 2012 at 11:24
It seems odd that you're not referencing the Inserted pseudo-table anywhere in your trigger.... you're basically upgrading everything - not just the rows that were in fact inserted.... — marc_s
– marc_s, Commented Nov 11, 2012 at 11:24
Uhmmm, Sorry but this is my first time using triggers. Can you please tell me if I'm doing something wrong? — user1553142
– user1553142, Commented Nov 11, 2012 at 11:25
Your trigger should do something based on the rows inserted - which are available in the Inserted pseudo table in your trigger. This one here just updates that other table ALL THE time and doesn't take into account / doesn't use any of the information from the rows that have been inserted.... So the question is: if you insert 10 new rows - what exactly do you want to do with those, in your trigger? What are you trying to achieve? — marc_s
– marc_s, Commented Nov 11, 2012 at 11:28

marc_s · Accepted Answer · 2012-11-11 11:59:21Z

1

When your INSERT trigger fires - then at least one new row has been inserted! That's a fact.

Now the question is: given that a single or multiple new rows have been inserted - what do you want to do with this knowledge??

Typically, you could e.g. set a column to a value you cannot specify as a default constraint - or you could insert the fact that the row has been inserted into an audit table or something....

So you'd have something like this:

CREATE TRIGGER sbhack_autoban
ON LOG_CONNECT201211
FOR INSERT
AS
    INSERT INTO LogAudit(InsertedDate, UserKey)
        SELECT 
             GETDATE(), i.User_Key
        FROM
             Inserted i

or something like that....

Update: ok, so you want to run that UPDATE statement when the rows have been inserted - not 100% clear, what columns/values from the inserted rows you want to use - looks like the e.UserKey column only - correct?

Then the UPDATE would be:

UPDATE login.dbo.USER_CHECK_LOGIN
SET login.dbo.USER_CHECK_LOGIN.CHECKLOGIN = 2
WHERE  
   login.dbo.USER_CHECK_LOGIN.USER_KEY IN 
          (SELECT USER_KEY FROM Inserted)
   AND login.dbo.USER_CHECK_LOGIN.CHECKLOGIN = 0
   AND login.dbo.USER_CHECK_LOGIN.USER_KEY != 51;

Update #2:

The point I still don't understand is : why do you want to run an update that uses the USER_CHECK_LOGIN, CHAR_DATA0 and LOG_USING_DEPOT201211 tables, when some rows are getting inserted into a totally separate, unrelated table LOG_CONNECT201211 ??

A trigger is used when you want to do something because rows have been inserted into that table - but in that case, you typically want to do something with the rows and their values that have been inserted...

I just don't see any connection between the rows being inserted into LOG_CONNECT201211 event, and the tables you are then querying from and updating. Where's the link?? WHY do you need to run *this UPDATE when data is inserted into LOG_CONNECT201211 ?? It would make sense if data where inserted into one of the tables involved in the UPDATE - but like this, it just totally doesn't make any sense .....

edited Nov 11, 2012 at 11:59

answered Nov 11, 2012 at 11:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

user1553142 Over a year ago

Yes, I want that if a row is inserted into the LOG_CONNECT201211 the update query will be triggered. hows that done?

marc_s Over a year ago

@user1553142: so what columns/values from the inserted rows do you want to use in your UPDATE query?

user1553142 Over a year ago

Hmmmmm. I think I'm doing this wrong, I don't want anything from the inserted rows.

marc_s Over a year ago

@user1553142: in that case, an INSERT trigger is definitely the wrong way to go !

marc_s Over a year ago

@user1553142: I would need to know when and what exactly you're trying to do - still not clear....

|

Collectives™ on Stack Overflow

SQL Server insert trigger not working

1 Answer 1

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related