sql trigger not work as expected

when one row is added to the customer table it should:

• copy to the other table called new_customer table
• delete the row in customer table.

In customer table has only one field that is Phone number.

This field should be copy into the new customer table and should be deleted from the customer table.

please can anyone tell me what's wrong with this trigger.....

CREATE TRIGGER update_cus
ON customer
FOR update AS
   IF (COLUMNS_UPDATED() & 1) > 0
      BEGIN

      INSERT INTO new_customer
         (Phone number
            )
         SELECT 'NEW',
            ins.Phone number
    开发者_如何学编程     FROM inserted ins

         SELECT 'OLD', 
            del.Phone number
         FROM deleted del

   END

---

My guess

CREATE TRIGGER update_cus
ON customer
FOR update AS
   IF EXISTS (SELECT * FROM INSERTED)
   BEGIN

      INSERT INTO new_customer ([Phone number])
         SELECT 'NEW', ins.[Phone number]
         FROM inserted ins

      DELETE customer WHERE
         [Phone number] IN (SELECT [Phone number] FROM deleted)

   END

---

You want behaviour for an INSERT but have defined the trigger for an UPDATE.

Your INSERT specifies one field, but you try to insert 2 values.

You state that you want to DELETE a record from customer, but don't have a DELETE statement.

I've refrained from writing a different trigger for you because I'm not actually 100% certain what you are trying to achieve.

Could you give examples, including what the various tables should look like before and after different actions?

EDIT

CREATE TRIGGER update_cus
ON customer
FOR insert AS
BEGIN

  INSERT INTO new_customer (name, id)
  SELECT name, id
  FROM inserted

  DELETE customer WHERE
  id IN (SELECT id FROM deleted)
  -- This assumes id is Unique, but as the table should normally be empty, that's fine.

END