SQL Server Performance

SQL Trigger (Basic)

Discussion in 'ALL SQL SERVER QUESTIONS' started by Michael Parks, Apr 24, 2012.

  1. Michael Parks New Member

    I know this function would likely be better accomplished by the software product I am using, but currently it doesn't allow....

    I want to create a trigger that will generate a RAISERROR when an appointment is created on a day that has passed. Essentially when appt_date (datetime) is less than GETDATE() on INSERT.

    Here is what I have so far:

    CREATETRIGGER dbo.appt_timeprior

    ON dbo.appt

    AFTERINSERT

    AS

    BEGIN

    SETNOCOUNTON;

    IF appt_date<GETDATE())

    RAISERROR(50001, 10, 1)

    END

    I think the IF line is the incorrect one. Not sure how to get this to a functional point.

    Any help is much appreciated!
  2. FrankKalis Moderator

    Welcome to the forum!
    SQL Server triggers make use of two pseudotables called "inserted" and "deleted", which are basically snapshots of the status before and after the change. If you refer simply to appt_date inside the trigger, SQL Server doesn't know what you mean by that. So, I think changing this line to
    Code:
    IF EXISTS (SELECT *
            FROM
                inserted
            WHERE
                appt_date < GETDATE())
        RAISERROR...                
    Another nice side effect of that is that it works for multi row inserts as well as for single-row ones. Not sure, if that is relevant to you, but it is good to be on the safe side, since SQL Server triggers fire once per statement and not once per row inserted.
  3. Michael Parks New Member

    Thanks! I got it working and does exactly what I hoped. I added a ROLLBACK TRANSACTION at the end since even though the error was raised, the appointment was still added to the schedule.

    Just want to make sure I have the correct usage and that the rollback transaction will only influence the appointment insert and no other data.

    CREATETRIGGER dbo.appt_timeprior
    ON [dbo].[appt]
    FOR INSERT
    AS
    BEGIN


    IF EXISTS (SELECT *
    FROM
    inserted
    WHERE
    appt_date < GETDATE())
    RAISERROR('This appointment occurs at a previous time or day. The appointment WILL NOT BE ADDED.',18, 1)
    ROLLBACK TRANSACTION
    END
  4. FrankKalis Moderator

    Welcome to the forum!
    This looks like MySQL, if I am not mistaken. If so, you might be better off, asking this in a MySQL community than a SQL Server community.

Share This Page