none
SQL Trigger After update RRS feed

  • Question

  • Hi Developers 

    hope all is well 

    just a question, im trying to create a trigger that will show how many records were loaded and the first date and last date the records were loaded at

    also i need to determine that if the same records were updated track the information as well

    the output im looking for is something like this 

    tablename recordcount mindate maxdate      operation
    customer 5000 2019/12/10 12:00 2019/12/10 12:15   INSERT

    tablename recordcount mindate maxdate      operation
    customer 2500 2019/12/10 12:20 2019/12/10 12:25   UPDATE

    this is the code i have used 


    ALTER TRIGGER [dbo].[utr_customer]
    ON [dbo].[customer]
    AFTER INSERT, UPDATE 
    AS 
    BEGIN 

    IF UPDATE([Address])
    BEGIN 
    INSERT INTO [dbo].[TrackDataLoad](TableName,RecordCount,FirstRecord_LoadDate,LastRecord_LoadDate,[Operation])
    SELECT
    '[dbo].[customer]' AS TableName,
    COUNT(*) AS RecordCount,
    MIN(CreatedDate) AS FirstRecord_LoadDate,
    MAX(CreatedDate) AS LastRecord_LoadDate,
    'UPDT' AS [Operation],

    FROM inserted
    END 

    ELSE

    BEGIN
    INSERT INTO [dbo].[TrackDataLoad](TableName,RecordCount,FirstRecord_LoadDate,LastRecord_LoadDate,[Operation])
    SELECT
    '[dbo].[customer]' AS TableName,
    COUNT(*) AS RecordCount,
    MIN(CreatedDate) AS FirstRecord_LoadDate,
    MAX(CreatedDate) AS LastRecord_LoadDate,
    'INS' AS [Operation],

    FROM inserted
    END
    END

    Will this code work for the desired output 

    Tuesday, December 10, 2019 5:47 AM

Answers

  • Hi , 

    I guess that you use many UPDATEs and many INSERTs to update and insert your data instead of one INSERT and one UPDATE. So you will get many rows in your target table . 

    So I think the result is right . And if your would like to get your result , please try to use one INSERT and one UPDATE to finish your data modification.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 7:04 AM

All replies

  • Yep, looks OK, have you tested the code?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 10, 2019 5:52 AM
    Answerer
  • Yes i did and the results sets are as follows 


    tablename recordcount mindate maxdate operation
    customer 1 2019/12/10 12:00 2019/12/10 12:00 INSERT
    customer 1 2019/12/10 12:01 2019/12/10 12:01 INSERT
    customer 1 2019/12/10 12:02 2019/12/10 12:02 INSERT
    customer 1 2019/12/10 12:03 2019/12/10 12:03 INSERT
    customer 1 2019/12/10 12:04 2019/12/10 12:04 INSERT
    customer 1 2019/12/10 12:05 2019/12/10 12:05 INSERT
    customer 1 2019/12/10 12:06 2019/12/10 12:06 INSERT
    customer 1 2019/12/10 12:07 2019/12/10 12:07 INSERT
    customer 1 2019/12/10 12:08 2019/12/10 12:08 INSERT
    customer 1 2019/12/10 12:09 2019/12/10 12:09 INSERT
    customer 1 2019/12/10 12:10 2019/12/10 12:10 INSERT
    customer 1 2019/12/10 12:11 2019/12/10 12:11 INSERT
    customer 1 2019/12/10 12:12 2019/12/10 12:12 INSERT

    this is not what i am looking for, im actually looking for something like this 

    tablename recordcount mindate maxdate      operation
    customer 5000 2019/12/10 12:00 2019/12/10 12:15   INSERT

    Tuesday, December 10, 2019 6:04 AM
  • Can you post sample data (CREATE TABLE + INSERT INTO) + desired result?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 10, 2019 6:23 AM
    Answerer
  • CREATE TABLE [dbo].[customer](
    [CustomerID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](255) NULL,
    [CustomerSurname] [nvarchar](255) NULL,
    [Address] [nvarchar](255) NULL,
    [LoadedDate] [datetime] NOT NULL
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[customer] ADD  DEFAULT (getdate()) FOR [LoadedDate]

    SET IDENTITY_INSERT [dbo].[customer] ON 
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (1, N'Ben', N'Jones', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (2, N'Cathy', N'Spark', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (3, N'Carl', N'Matthew', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (4, N'Donovan', N'Anthony', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (5, N'Margret', N'King', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (6, N'Jermaine', N'Benson', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (7, N'Yvonne', N'Johnson', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    INSERT [dbo].[customer] ([CustomerID], [CustomerName], [CustomerSurname], [Address], [LoadedDate]) VALUES (8, N'Samantha', N'Parks', NULL, CAST(N'2019-12-10T08:44:01.407' AS DateTime))
    GO
    SET IDENTITY_INSERT [dbo].[customer] OFF
    GO

    Tuesday, December 10, 2019 11:10 AM
  • If I do two inserts  on customer table my TrackDataLoad table will have two rows for that operation for example based on your trigger

    customer 1 2019/12/10 12:01 2019/12/10 12:01 INSERT
    customer 1 2019/12/10 12:15 2019/12/10 12:15 NSERT

    So if you want at end of  the day to get all over into you need to query TrackDataLoad 

    SELECT  tablename,operation,sum(recordcount) all_recordcount, dateadd(day,datediff(day,0,FirstRecordLoadDate),0) day_ofoper

    from TrackDataLoad 

    group by tablename,operation,dateadd(day,datediff(day,0,FirstRecordLoadDate),0) 



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Tuesday, December 10, 2019 11:43 AM
    Answerer
  • Hi , 

    I guess that you use many UPDATEs and many INSERTs to update and insert your data instead of one INSERT and one UPDATE. So you will get many rows in your target table . 

    So I think the result is right . And if your would like to get your result , please try to use one INSERT and one UPDATE to finish your data modification.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 7:04 AM
  • Hi 

    That makes more sense, thank you so much 

    Wednesday, December 11, 2019 7:07 AM