CODEWITHSUNDEEP
sqlsql-servertriggersinsert
user1553142
user1553142

Reputation: 237

SQL Server insert trigger not working

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.

Upvotes: 0

Views: 6977

Answers (1)

marc_s
marc_s

Reputation: 755297

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 .....

Upvotes: 1

Related Questions