How triggers will work?

Question

Using SQL Server 2000

I make a trigger in new database

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)

The above query is executing successfully? But rows was not affected in the old database.

I insert a new row in new database then i checked in old database, nothing affected?

How to get a data's from other database?

Answer 1

I think you need that:

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

The 'Table' inserted contains all new data, 'Table' deleted containers deleted data. When an update occurs both 'Tables' are filled.