How do i do this in a trigger SQL Server.

Question

The first line seems to be correct. The second line was my SQLite code. With code i get an exception about an error near trigger. In VS it says the multipart identifier could not be bound. With SQLite the new stands for the row being insert. So i would like to increase the count of whomever the subscription recipient is. How do i do that with a SQL Server trigger?

CREATE TRIGGER  trig_0  ON  subscription   
 AFTER INSERT  AS  
UPDATE user_data 
   SET msg_count = msg_count + 1 
 WHERE id = new.recipient; 

Answer 1

There is no magic 'new' in SQL Server. There is a magic INSERTED, and is a table:

CREATE TRIGGER  trig_0  ON  subscription   
 AFTER INSERT  AS  
UPDATE user_data 
   SET msg_count = msg_count + 1 
FROM user_data
JOIN INSERTED ON id = INSERTED.recipient; 

Answer 2

i think there is inserted and delted tables used by the trigger not new '''

3> CREATE TRIGGER myTriggerINSERT
4> ON Employee
5> FOR INSERT
6> AS
7> DECLARE @ID int, @Name nvarchar(30)
8>
9> SET @ID = (SELECT ID FROM inserted)
10> SET @Name = (SELECT Name FROM inserted)

for more detail : http://www.java2s.com/Code/SQLServer/Trigger/Getvaluefromupdatedinsertedanddeleted.htm

Answer 3

assuming subscription has the column recipient which you can join with user_data.id, here is one way, you can use the inserted pseudo table to join back

CREATE TRIGGER  trig_0  ON  subscription   
 AFTER INSERT  AS  
UPDATE user_data 
   SET msg_count = msg_count + 1 
 WHERE exists (Select * from inserted i where user_data.id = inserted.recipient)