Trigger - Insert 1

[M8KWR]

Not really used triggers before, so please bare with me.

I want to update a field in a table, every time that table has a new insert.

From the row that is inserted, i want to take the value from the column (this will always be populated with something)

Then query that against an sql statement, using the value in the where section.

This will only bring back 1 column, with 1 row, and then use that value to update a column in the original table.

Does that make sense!!!!

What i am doing, each time a user log onto the network i store the ip address and username into a table (this is the information i am looking to grab, in the where statement).

I then get syslogs from the firewall, which is stored in another table in the same database, i want to update the row with the username, from the User_Table.

Many thanks for reading.

 

[bborissov]

Something like:

CREATE TIGGER MyTrigger OF MyTableName
       FOR INSERT
AS
  BEGIN
       UPDATE YourTable SET SomeFIeld = Ins.OtherField
       FROM YourTabe
       INNER JOIN INSERTED Ins ON ????????
  END

Remember ALWAYS write your triggers so they can handle multiple rows. Trigger fires AFTER the batch is complete and you never know how many rows are affected in that batch.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[M8KWR]

I have written the following trigger statement, but it seems to be updating the whole column and not just the inserted records, could anyone advise where i have gone wrong.

ALTER TRIGGER [dbo].[New_Trigger] ON [dbo].[test]
FOR INSERT
AS
  BEGIN
       UPDATE TEST SET test.Name = Table_Users.username
       FROM 
       (SELECT     TOP 100 PERCENT dbo.User_Table.UserName, dbo.User_Table.IP_Address
FROM         dbo.User_Table INNER JOIN
                          (SELECT     TOP 100 PERCENT IP_Address, MAX(Date1) AS Expr1
                            FROM          dbo.User_Table AS User_Table_1
                            GROUP BY IP_Address
                            ORDER BY MAX(Date1) DESC) AS Recent_Logon ON dbo.User_Table.IP_Address = Recent_Logon.IP_Address AND
                      dbo.User_Table.Date1 = Recent_Logon.Expr1
ORDER BY dbo.User_Table.UserName) as Table_Users
       
       INNER JOIN INSERTED ON Table_Users.ip_address =  inserted.ipno

  END

 

[bborissov]

Try with this (not tested AT ALL)

[COLOR=blue]UPDATE[/color] TEST [COLOR=blue]SET[/color] test.Name = Table_Users.username
       [COLOR=blue]FROM[/color] Test
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] dbo.User_Table.UserName,
                          dbo.User_Table.IP_Address
                   [COLOR=blue]FROM[/color] dbo.User_Table
                   [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] IP_Address, [COLOR=#FF00FF]MAX[/color](Date1) [COLOR=blue]AS[/color] Expr1
                                      [COLOR=blue]FROM[/color] dbo.User_Table
                               [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] IP_Address
                               [COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] [COLOR=#FF00FF]MAX[/color](Date1) [COLOR=#FF00FF]DESC[/color]) [COLOR=blue]AS[/color] Recent_Logon
                         [COLOR=blue]ON[/color] dbo.User_Table.IP_Address = Recent_Logon.IP_Address AND
                            dbo.User_Table.Date1      = Recent_Logon.Expr1) [COLOR=blue]as[/color] Table_Users
             [COLOR=blue]ON[/color] Test.IpNo = Table_Users.ip_address
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] INSERTED [COLOR=blue]ON[/color] Test.ip_address =  inserted.ipno

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

By the way "ORDER BY MAX(Date1) DESC" is redundant also.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[M8KWR]

Many thanks