Trigger for automatically date

Transact-SQL
1

Hello everyone,

I created a trigger that inserts the automatic time in the Date column as soon as an update is made in the Comments text field.

The problem here is, , that it constantly overwrites all updates that have already been carried out. It should only add the new date to the article that is currently being carried out.

Can someone help me with this and make an appropriate adjustment to the code?

ALTER TRIGGER [dbo].[time_trigger] 
ON [dbo].[products]
AFTER UPDATE
AS 
BEGIN
Update dbo.products
SET DATE= GETDATE()
WHERE COMMENT IN (Select Distinct COMMENT FROM products)
END

If I now run an update to product number 888 with the text Hello, the date 27-12-2022 appears with the time 11:00

If I carry out another update with the product number 999 with the text Bye, the 27-12-2022 with the time 12:35 appears.

The problem here is that the date for item 888 is also now 12:35. However, this should not be overwritten.

How it Should be after an Update of an other product:
image

2

Please provide the DDL/structure of the table...
and the query you are using to update .
Any trigger have 2 tables INSERTED and DELETED tables that hold the newly record and old record (before update).

With out knowing your structure , try this statement

Update p
SET p.DATE= GETDATE()
FROM 
   dbo.products as p
   INNER JOIN INSERTED as i
    oN p.customerNr= i.CustomerNr
     AND p.productNr = i.productNr
WHERE
   p.COMMENT IN (Select Distinct COMMENT FROM INSERTED)

Definitely, this statement could be written different, but without knowing your structure of the table (for example the join will be on primary key if exist)

3

Hello @stepson ,

this is the only table in the entire database. I havent more tables which I use.
I need this for testing purposes.

This is how I created the table:

CREATE TABLE products (
   customerNr int,
   productnr int,
   comment varchar(30),
   automatic_date smalldatetime,
   primary key (customerNr, productNr),
   )

Here is the query which I use for update:

UPDATE products SET COMMENT = 'Hello'
WHERE customerNr  = 11003 and productNr = 888
4

Try this :

ALTER TRIGGER [dbo].[time_trigger] 
ON [dbo].[products]
AFTER UPDATE
AS 
BEGIN
     Update p
     SET p.DATE= GETDATE()
     FROM 
        dbo.products as p
        INNER JOIN INSERTED as i
             ON p.customerNr= i.customerNr
             AND p.productNr = i.productNr
END
1 Like
5

@stepson Thank you. Work perfectly.

1 Like