将值复制到新表并从旧表中删除值

Question

CREATE TRIGGER trigg_msg 
ON messagein 

FOR insert,delete,update AS 
IF EXISTS (SELECT * FROM INSERTED) 
BEGIN 

INSERT INTO new_message (Phone number,CusID,CusName,CusAddress) values ( SELECT ins.sender FROM inserted ins, SELECT cus.CusID FROM customer cus, SELECT cusname FROM customer cus, SELECT cus.cus_phone number FROM customer cus, SELECT cus.Address FROM customer cus) 
DELETE * FROM messagein 
END 

这个触发器不起作用。请有人告诉我错误......

当插入一行到 messagein 表时，它应该复制 sender 字段的值并将其复制到 new_message 表。然后其他值如 cusID，CusName，.. 应该从客户表中检索。请有人帮助我。

更新：

CREATE TRIGGER trigg_msg 

ON messagein 

FOR insert

IF EXISTS (SELECT * FROM INSERTED)
 
BEGIN
 
INSERT INTO new_message(Phone_number,CusID,CusName,CusAddress) values ( SELECT ins.sender FROM inserted ins, SELECT cus.CusID, cus.cusname, cus.cus_phone_number,  cus.Address FROM customer cus)

DELETE FROM messagein 

@Devart 和@TudorConstantin 非常感谢你们对我的帮助。最后我想出了这个触发器，但仍然出现错误。

Answer 1

您至少有一个语法错误：

DELETE * FROM messagein 

应该是：

DELETE FROM messagein 

另外，插入应该是：

INSERT INTO new_message (Phone_number,CusID,CusName,CusAddress) values ( SELECT ins.sender FROM inserted ins, SELECT cus.CusID, cus.cusname, cus.cus_phone_number,  cus.Address FROM customer cus)