question

derbellaroussi-9565 avatar image
0 Votes"
derbellaroussi-9565 asked EchoLiu-msft commented

Automatic insert sql

I have a view in my database where data is stored, it has 2 columns (ID and value) and 3 rows (ID1, 2 and 3), the ID never changes but the value column of this ID always has a new value (e.g. ID : 1 value : False; ID :2 value : 55), the ID1 always changes from True to False and ID2 and 3 always have new values
I have created a new table with 2 columns (ID and Value).
I want to write a program that every time the value of ( ID 1 ) changes from False to True in the view, a new row is automatically inserted in the new table with the value of ID2 and ID3).

this is my program :
INSERT INTO Table1 ( ID,Value )
SELECT dbo.View1.Value , View_1.Value ,
FROM dbo.View1 CROSS JOIN
dbo.View1 AS View1_1

WHERE (dbo.View1ID = 2)AND (View1_1.ID = 3)

it does what I want, but only when I run the program, I want this insert function to happen automatically every time the ID1 in the view changes from false to true. can you please help me

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

You would have to do that with triggers on the underlying tables in the view. And that is about all I can say, since I don't know the definition of this view. Maybe you could share it?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @derbellaroussi-9565,

Welcome to the microsoft TSQL Q&A forum!

As Erland said, you need to create a trigger on the underlying tables in the view, please refer to:

 CREATE TRIGGER tinsert
 ON yourtable 
 FOR UPDATE 
 As 
 IF UPDATE(Value)
 BEGIN
 INSERT INTO Table1 (ID,Value)
 SELECT ID,Value
 FROM View1 
 WHERE ID=2 OR ID=3
 END

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.