Share via

Set a trigger in SQL

Sarath 106 Reputation points
Nov 23, 2020, 9:40 AM

HI Team,

I have table which has ID, Start Time and End time.
Start time will be captured through app and insert into table.
To calculate End time I have created a stored procedure.

As soon as Start Time entered into table we need to trigger to call the stored procedure to calculate end time.

Please help how to create a trigger in SQL and call the store proc (SP_Calculate_Endtime)
41881-image.png

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,663 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Viorel 118.4K Reputation points
    Nov 23, 2020, 11:09 AM

    If you just perform appropriate calculations, then consider an alternative: Computed Columns. Convert your stored procedure to function, for example:

    create function dbo.CalculateEndTime ( @StartTime datetime )  
    returns datetime     
    begin 
        declare @EndTime as datetime
    
        -- TODO: calculate EndTime
    
        set @EndTime = DATEADD(d, 4, @StartTime)
    
        return @EndTime
    end
    

    Then you can define the table in this manner:

    create table MyTable 
    (
        id int, 
        StartTime datetime, 
        EndTime as dbo.CalculateEndTime(StartTime)
    )
    

    After you insert values to id and StartTime and execute select EndTime, … from MyTable, you will get the calculated values.

    However, you cannot perform certain operations in this function.

    Do you really need a trigger, which appears to be more complex?


  2. Viorel 118.4K Reputation points
    Nov 23, 2020, 11:50 AM

    If you cannot use Computed Columns (cannot change the existing table) and need a trigger, then try something like this:

    create or alter trigger MyTrigger 
    on MyTable
    for insert, update
    as
    begin
        update t
        set t.EndTime = dbo.CalculateEndTime(t.StartTime)
        from MyTable t
        inner join inserted on inserted.id = t.id 
    end
    

    where CalculateEndTime is the existing function.

    Use if UPDATED(‘StartTime’) to identify the changed column and avoid unneeded updates if your table has more columns.


  3. Erland Sommarskog 113.2K Reputation points MVP
    Nov 23, 2020, 11:00 PM

    I agree with the rest of the folks. Scrap the procedure, and make EndTime a computed column:

    EndTime as dbo.ufn_ADD_WORKING_DAYS( CAST(A.StartTime AS DATE),
                                         CASE StageNumber WHEN 1 THEN 2
                                                          WHEN 2 THEN 8
                                                          WHEN 3 THEN 5
                                                          WHEN 4 THEN 7
                                          END)
    

    Or use the trigger that Tom posted.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.