How to update existing rows via trigger with C# in SQL

Question

I have a table in SQL called EmpLog which contains the 'start' and 'end' data for employees. I'm quite confused as to how I'm going to change the variables within the trigger during update since it has to cater to all different employees. Do I need to alter the trigger via sql parameter in c# for every insertion? or is there an alternative to keep it minimal and efficient? Thank you in advance. Since as far as I'm able to understand triggers are hard coded onto the database, which is changeable through 'ALTER'. What can I do to change existing row's with different IDs via trigger?

create trigger TrgEmpLog on EmpLog
AFTER UPDATE
AS
declare @shiftstart time;
declare @shiftend time;
declare @totalminutes decimal(18,2);

IF EXISTS (SELECT shiftend FROM EmpLog WHERE EmpID = "C# Variable" and CONVERT(date,LogDate) = CONVERT(date, GETDATE()) and shiftend is not null)
    BEGIN  
        IF EXISTS (SELECT EmpLog.TotalMinutes from EmpLog WHERE EmpID = "C# Variable" and CONVERT(date,LogDate) = CONVERT(date, GETDATE()) and TotalMinutes is not null)
            BEGIN
                ROLLBACK TRANSACTION
            END 
                ELSE
                select @shiftstart=EmpLog.ShiftStart from EmpLog where EmpID = "C# Variable" and CONVERT(date,LogDate) = CONVERT(date, GETDATE()) and TotalMinutes IS NULL; 
                select @shiftend=EmpLog.ShiftEnd from EmpLog where EmpID = "C# Variable" and CONVERT(date,LogDate) = CONVERT(date, GETDATE()) and TotalMinutes IS NULL;     
                select @totalminutes=DATEDIFF(MINUTE,@shiftstart, @shiftend);   
                UPDATE EmpLog
                SET TotalMinutes=@totalminutes/60.00
                WHERE EmpID= "C# Variable"and CONVERT(date,LogDate) = CONVERT(date, GETDATE());
    END
        ELSE
            BEGIN
                ROLLBACK TRANSACTION
            END

And the code that I'm using to prompt the trigger is:

UPDATE EmpLog
SET ShiftEnd = 'current time'
WHERE EmpID='C# variable' and CONVERT(date,LogDate) = CONVERT(date, GETDATE());

This code below ends in a trigger, but it works when I remove the 'and logDate = getdate(). UPDATE EmpLog SET ShiftEnd = '09:00:00' WHERE EmpID = 1 and CONVERT(date, LogDate) = CONVERT(date, GETDATE())

I'm sorry for being unclear, I'm unfamiliar with SQL. I'm unable to understand the logic how I'm going to be able to utilise trigger with differing 'WHERE' variables when it's as far as I understand is hard coded onto the database. — user3442765
– user3442765, Commented Jul 24, 2016 at 19:20
You cannot pass parameters to a trigger. You can do that with a stored procedure. If your logic is complex, and yours seems to be, a stored procedure is where this should be. Triggers are meant for lighter weight operations that must run when data is inserted, updated or deleted, like updating a user ID and updated date on a record. Your TotalMinutes field may even lend itself to a calculated field on the table. — R. Richards
– R. Richards, Commented Jul 24, 2016 at 19:30
I don't agree. Triggers can be very complex. You can do anything in a trigger. It's just a stored procedure that has a virtual table with the affected rows implicitly available. Further more, it gives the possibility to rollback the data manipulation. Don't alter the trigger. Use a logic in the rigger that depends takes into account the several scenarios. — Massimiliano Carosi
– Massimiliano Carosi, Commented Jul 24, 2016 at 19:55
@Massimiliano Carosi How would you suggest I make the trigger to work with different variables? — user3442765
– user3442765, Commented Jul 24, 2016 at 21:27

R. Richards · Accepted Answer · 2016-07-24 22:34:08Z

1

This code below worked for me with some quick testing. Maybe it will work for what you need. The actual trigger part is probably all you really need, the rest I was using to test.

The way this is written, it will only update the current, and relevant record (the one being updated at the time). Joining to the inserted table makes sure that happens. Use the inserted and deleted Tables (MSDN)

CREATE TABLE EmpLog (EmpID int, ShiftStart time, Shiftend time, LogDate date, TotalMinutes decimal(18,2));
GO

-- Trigger starts here
CREATE TRIGGER TrgEmpLog ON EmpLog
AFTER UPDATE
AS

IF EXISTS (SELECT * 
    FROM EmpLog A JOIN inserted B 
    ON A.EmpID = B.EmpID
    WHERE CONVERT(date, A.LogDate) = CONVERT(date, GETDATE()) 
        AND A.shiftend IS NOT NULL)
BEGIN  
    IF EXISTS (SELECT * 
            FROM EmpLog A JOIN inserted B 
            ON A.EmpID = B.EmpID        
            WHERE CONVERT(date, A.LogDate) = CONVERT(date, GETDATE()) 
            AND A.TotalMinutes IS NOT NULL)
        BEGIN
            ROLLBACK TRANSACTION
        END 
            UPDATE EmpLog
            SET TotalMinutes=DATEDIFF(MINUTE, A.ShiftStart, A.Shiftend)/60.00
            FROM EmpLog A JOIN inserted B 
            ON A.EmpID = B.EmpID;
END
ELSE
BEGIN
    ROLLBACK TRANSACTION
END

GO
-- Trigger End here

INSERT INTO EmpLog (EmpID, ShiftStart, Shiftend, LogDate) VALUES (1, '00:00:00', NULL, GETDATE()),
    (2, '08:00:00', NULL, GETDATE()),
    (3, '16:00:00', NULL, GETDATE());

SELECT * FROM EmpLog;

UPDATE EmpLog SET Shiftend = '08:00:00' WHERE EmpID = 1;

SELECT * FROM EmpLog;

DROP TABLE EmpLog;

You may have to play around with some of the IF EXISTS, if they don't follow you logic exactly.

As @massimiliano mentioned in the comments, triggers can get quite complicated if you want them to. For me, I avoid that. The trigger is always one of the last places I think to look when troubleshooting issues. Personal preference!

Good Luck!

answered Jul 24, 2016 at 22:34

R. Richards

25.2k10 gold badges67 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3442765 Over a year ago

Though I wonder why it says "Transaction ended in a trigger. Batch has been aborted" when I add 'LogDate' to WHERE. I'll edit it onto the original post

R. Richards Over a year ago

You may want to think about removing the ROLLBACK TRANSACTION statements from this. I left them in because I thought you might have a reason to keep them. The trigger, as it is written, will only do an update if the IF EXISTS criteria is met, so there is no need to roll anything back if it isn't.

Collectives™ on Stack Overflow

How to update existing rows via trigger with C# in SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related