触发器如何工作？

Question

使用 SQL Server 2000

我在新数据库中创建一个触发器

CREATE TRIGGER [CARDEVENTSAVING] ON [dbo].[T_CARDEVENT] 
AFTER INSERT 
AS

DECLARE @EDATE VARCHAR(50)
DECLARE @ETIME VARCHAR(50) 
DECLARE @EDOOR NVARCHAR(50)
DECLARE @EFLOOR NVARCHAR(50)
DECLARE @EMPID NVARCHAR(50)
DECLARE @TAG NVARCHAR(50)
DECLARE @ENAME NVARCHAR(50)
DECLARE @ELNAME NVARCHAR(50)
DECLARE @EPART  NVARCHAR(50)
DECLARE @EDEP NVARCHAR(50)
DECLARE @EFUNCTION NVARCHAR(50)
DECLARE  @STATUS NVARCHAR(50)
DECLARE @EDATE2 DATETIME


SELECT @EDATE =(SELECT CARDEVENTDATE FROM INSERTED)
SELECT @ETIME =(SELECT CARDEVENTTIME FROM INSERTED)
SELECT @TAG = (SELECT CARDNO FROM INSERTED)

IF LEN(@TAG) = 4
 SELECT @TAG = '0000'+@TAG  
ELSE IF LEN(@TAG) = 5
  SELECT @TAG ='000'+@TAG
ELSE IF LEN(@TAG) = 6
  SELECT @TAG = '00' + @TAG
ELSE IF LEN(@TAG) = 7
  SELECT @TAG = '0' + @TAG

SELECT @EDOOR = (SELECT DOOR FROM T_PERSONALTRACKING WHERE CARDNO = @TAG)
SELECT @EFLOOR = (SELECT FLOOR FROM T_PERSONALTRACKING WHERE CARDNO = @TAG)
SELECT @EMPID = (SELECT SABUN FROM DATALINEDB.DBO.PERSON WHERE TAGNO = @TAG)
SELECT @ENAME = (SELECT NAME FROM DATALINEDB.DBO.PERSON WHERE TAGNO = @TAG)
SELECT @ELNAME = (SELECT LastName FROM DATALINEDB.DBO.PERSON WHERE TAGNO = @TAG)
SELECT @EPART = (SELECT PART FROM DATALINEDB.DBO.PERSON WHERE TAGNO = @TAG)
SELECT @EDEP = (SELECT MBUSO FROM DATALINEDB.DBO.PERSON WHERE TAGNO = @TAG)
SELECT @EFUNCTION = (SELECT FUNCTIONKEY FROM INSERTED)
SELECT @EDATE2 =(SELECT CARDEVENTDATE FROM INSERTED)

INSERT INTO 
  FINGERPRINTDB.DBO.HISTORY(EDATE, ETIME, EDOOR, EFLOOR, ESABUN, ETAG, 
                            ENAME, ELNAME, EPART, EDEP, ESTATUS, EFUNCTION, 
                            EINOUT, EDATE2) 
VALUES
  (@EDATE, @ETIME, @EDOOR, @EFLOOR, @EMPID, @TAG, 
   @ENAME, @ELNAME, @EPART, @EDEP, NULL, @EFUNCTION, 
   NULL, @EDATE2)

上述查询是否成功执行？但旧数据库中的行不受影响。

我在新数据库中插入一个新行，然后检查旧数据库，没有任何影响吗？

如何从其他数据库获取数据？

Answer 1

我认为您需要：

CREATE TRIGGER [CARDEVENTSAVING] ON [dbo].[T_CARDEVENT] 
AFTER INSERT
AS
INSERT oldDB.DBO.HISTORY (id, event, ...)
SELECT id, event, ... 
FROM inserted

插入的“表”包含所有新数据，删除的“表”包含删除的数据。当更新发生时，两个“表”都被填满。