Trigger to Update a Field

Question

Hi,

I'm having trouble writing a trigger which to me seems very easy but for whatever reason I just cannot get it to work.

I have two tables ( Table1, Table2 ), Table1 has a Primary Key of ID and Table2 has a Foreign Key of IDFK. A record is inserted into both Table1 and Table2 and I need to copy the data from Table1.dtLicence into Table2.dtOriginalLicence After Insert.

This is what I have...

CREATE TRIGGER [dbo].[trg_OriginalLicenceDate] 
   ON  [dbo].[Table2] 
   AFTER INSERT
AS 
BEGIN

 SET NOCOUNT ON;

  Update T2
  set T2.dtOriginalLicence = T1.dtLicence 
  From Table2 AS T2
  INNER JOIN Table1 AS T1 ON T2.IDFK = T1.ID
  INNER JOIN inserted AS i on T2.IDFK = i.IDFK

END

Any help would be appreciated, thank you!!

Answer 1

But what is the primary key of T2? If IDFF is non-unique, the join between T2 and inserted will produce multiple rows, and which row that is selected as the source row is not deterministic.

Answer 2

Hi @Testsubjec ,

Welcome to Microsoft Q&A!

We recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, and the complete process you did.

Please refer below one simple example:

One: DDL,insert statement and add foreign key:

drop table if exists table2   
drop table if exists table1   
  
create table table1   
(ID int primary key,  
Col1 varchar(10),  
dtLicence varchar(10))  
  
create table table2   
(ID int primary key,  
IDFK int,  
Col1 varchar(10),  
dtOriginalLicence varchar(10))  
  
ALTER TABLE table2  
ADD FOREIGN KEY (IDFK) REFERENCES table1(ID);  

Two: Insert one row of sample data before trigger created:

insert into table1 values  
(1,'A','L1')  
  
insert into table2 values  
(101,1,'A',NULL)  

Three: Create your trigger:

CREATE TRIGGER [dbo].[trg_OriginalLicenceDate]   
    ON  [dbo].[Table2]   
    AFTER INSERT  
 AS   
 BEGIN  
      
  SET NOCOUNT ON;  
      
   Update T2  
   set T2.dtOriginalLicence = T1.dtLicence   
   From Table2 AS T2  
   INNER JOIN Table1 AS T1 ON T2.IDFK = T1.ID  
   INNER JOIN inserted AS i on T2.IDFK = i.IDFK  
      
 END  

Four: Insert new sample data:

 insert into table1 values  
(2,'B','L2')  
  
insert into table2 values  
(102,2,'B',NULL)  

Five: Check the result as below.

select * from table1  
select * from table2  

As you could see, the dtOriginalLicence of second row was updated after trigger created.

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @MelissaMa-MSFT ,

Table 1 and Table 2 hold licence information and there will only be one record per entity. The information is recorded across two tables due to the amount of fields.

After some investigation I think I have got to the bottom of it. I created two triggers on each table ( one insert, one update ) to see exactly what was happening.

From the image below Row 22 and 21 are the initial inserts and the Licence Date is null, there is an update on row 20 which populates the Licence Date and then a number of updates there after.

Not sure how I'm doing to capture the initial Licence Date and copy this into the Original Licence Date.