一个简单的数据库trigger报错

Question

那么麻烦。直接写一个
SELECT * INTO XXX FROM INSTERed 不就成啦！
在DELETED里写一个
DELETE FROM XX WHERE ID=DELETE.ID

Answer 1

INSERT INTO CustomerAudit (caCustomerID, 
                                  caTransType, 
                                  caUser, 
                                  caAudDateTime) 
                VALUES            (@customerID, 
                                  @caTransType, 
                                  [USER],  --user加上[]试一下。
                                  GETDATE());  

Answer 2

CREATE TABLE Customer 
( 
    customerID                    INT  IDENTITY(1000000,1), 
    customerFirstName             VARCHAR(15),        
    customerLastName              VARCHAR(15)        NOT NULL, 
    customerHouseNum              CHAR(5), 
    customerStreet                CHAR(15), 
    customerCity                  CHAR(15), 
    customerState                  CHAR(15),    
    customerRegisterDate          DATETIME, 
    customerZipCode                CHAR(6),    
    CONSTRAINT CustomerPK  PRIMARY KEY (customerID), 
); CREATE TABLE CustomerAudit 
( 
    caTransID                     INT        IDENTITY (1,1), 
    caCustomerID                  INT, 
    caTransType                    CHAR(2), 
    caUser                        VARCHAR(10), 
    caAudDateTime                  DATETIME    DEFAULT GETDATE(),    
    CONSTRAINT CustomerAuditPK      PRIMARY KEY (caTransID), 
); create TRIGGER CustomerTrigger ON Customer 
FOR INSERT, DELETE 
AS 
DECLARE 
  @customerID    INT, 
  @caTransType   CHAR 
SET @caTransType = 'I'; 
SELECT @customerID = customerID 
FROM INSERTED; IF @customerID IS NULL 
   BEGIN 
     SET @caTransType = 'D' 
     SELECT @customerID = customerID FROM DELETED; 
   END 
 ELSE 
   BEGIN 
     SELECT @customerID = customerID FROM INSERTED; 
   END 
   
INSERT INTO CustomerAudit 
  (caCustomerID, 
   caTransType, 
   caUser, 
   caAudDateTime) 
VALUES (@customerID, 
        @caTransType, 
        USER, 
        GETDATE());  
goinsert Customer
  select 'John','chen','001','001','CZ','CC','2005-01-01','000001' 
  
select * from CustomerAudit
/*
caTransID   caCustomerID caTransType caUser     caAudDateTime
----------- ------------ ----------- ---------- -----------------------
1           1000000      I           dbo        2009-05-02 14:54:44.730(1 行受影响)
*/delete Customerselect * from CustomerAudit
/*
caTransID   caCustomerID caTransType caUser     caAudDateTime
----------- ------------ ----------- ---------- -----------------------
1           1000000      I           dbo        2009-05-02 14:54:44.730
2           1000000      D           dbo        2009-05-02 14:56:05.043(2 行受影响)
*/我测试一下，没报错。