Trigger to update a table with timestamp when data is inserted into another table

Question

I am very new to triggers. need help here.

I have two tables, [dbo].[Demand_Request] and [dbo].[Modified_Demand_Request].

CREATE TABLE [dbo].[Demand_Request] 
(
    [CASE_ID] [INT] NULL,
    [TE_PART_NUMBER] [NVARCHAR](50) NULL,
    [VALUE] [FLOAT] NULL,
    [DEMAND_DATE] [DATETIME] NULL
) 

CREATE TABLE [dbo].[Modified_Demand_Request] 
(
    [CASE_ID] [INT] NULL,
    [TE_PART_NUMBER] [NVARCHAR](50) NULL,
    [VALUE] [FLOAT] NULL,
    [DEMAND_DATE] [DATETIME] NULL,
    [Modified_On] [DATETIME] NULL
)

When data is inserted into [dbo].[Demand_Request], I want to add the same data into table [dbo].[Modified_Demand_Request] along with a timestamp.

Below is my trigger, but it is not working:

CREATE TRIGGER [dbo].[Modified_Demand_Request1]
ON [dbo].[Demand_Request]
AFTER UPDATE
AS
     INSERT INTO [dbo].[Modified_Demand_Request] ([CASE_ID], [TE_PART_NUMBER],[VALUE], [DEMAND_DATE], [Modified_On])
         SELECT 
             [CASE_ID], [TE_PART_NUMBER], [VALUE],
             [DEMAND_DATE], GETDATE()
         FROM 
             INSERTED

You trigger is defined as `after update` so it won't trigger on `insert`. For that you need `after insert, update` — Dale K, Nov 28 '18 at 04:03
Possible duplicate of [Sql Server trigger insert values from new row into another table](https://stackoverflow.com/questions/2247679/sql-server-trigger-insert-values-from-new-row-into-another-table) — Mani Deep, Nov 28 '18 at 04:16

Mani Deep · Answer 1 · 2018-11-29T06:52:34.183

0

Try this FOR INSERT

Create Trigger [dbo].[Modified_Demand_Request1]
   on [dbo].[Demand_Request]
   FOR INSERT
   AS
     Insert Into[dbo].[Modified_Demand_Request] ([CASE_ID],[TE_PART_NUMBER],[VALUE],[DEMAND_DATE],[Modified_On])
       SELECT [CASE_ID],[TE_PART_NUMBER],[VALUE],[DEMAND_DATE],GETDATE()
       FROM INSERTED

for DEMO REXTESTER

Extra Reference: Firing Trigger for Bulk Insert

edited Nov 29 '18 at 06:52

answered Nov 28 '18 at 04:10

Mani Deep

1,298
2
17
33

@parthaburagohain I have updated the answer with demo link, i hope it is working as expected, can you please elaborate your issue? – Mani Deep Nov 28 '18 at 07:32
hi, The trigger works when i am trying to insert one line. but when i run the it for multiple lines , it does not work.. How can i achieve using bulk insert ? – partha buragohain Nov 28 '18 at 08:34
@parthaburagohain what happens when you insert multiple rows? The code looks correct as its selecting from `inserted` which will contain all inserted rows. – Dale K Nov 28 '18 at 09:25
Nothing is copied from the base table when i try to insert multiple rows. – partha buragohain Nov 28 '18 at 09:29
1

Hmmm... might require some investigation... you can debug a trigger using select statements and print statements if you insert the data from SSMS and from that work out what is/isn't happening. – Dale K Nov 28 '18 at 09:35
@parthaburagohain may I know how are you inserting data into table? using `select` statement or a simple `insert` statement?, triggers fire once per statement, not once per row. – Mani Deep Nov 29 '18 at 06:44
or try using a cursor? insert values into a cursor from `INSERTED` and run look for the cursor? might help, however using of cursors is not advised. – Mani Deep Nov 29 '18 at 06:46

score 0 · Answer 2 · answered Nov 28 '18 at 18:09

May I know that how you are inserting the data. I tried the same as below (with "FOR INSERT" trigger) and able to get the data in Modified_Demand_Request table.

    insert into [Demand_Request] values(1,'Test',1.0,GETDATE())
    insert into [Demand_Request] values(2,'Test1',1.0,GETDATE())
    insert into [Demand_Request] values(3,'Test2',1.0,GETDATE())
    insert into [Demand_Request] values(14,'Test2',1.0,GETDATE())

Trigger to update a table with timestamp when data is inserted into another table

2 Answers2