Have Geek Brain

How to do it in a SQL Server trigger

The first line seems to be correct. The second line is my SQLite code. With the code, I am getting an error about the trigger. VS says the multipart id cannot be linked. With SQLite, new denotes row insertion. Therefore, I would like to increase the number of users who receive a subscription. How can I do this using SQL Server startup?

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


a source to share


3 answers


There is no magic "new" in SQL Server. There is a magic INSERTED and 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;
+7


a source


I think there are inserted and delted

tables used by 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 details: http://www.java2s.com/Code/SQLServer/Trigger/Getvaluefromupdatedinsertedanddeleted.htm

+1


a source


Assuming the subscription has a column receiver that 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)
0


a source






More articles:


All Articles