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. JDizajn New Member

    DELIMITER $$

    DROP PROCEDURE IF EXISTS `lunasoft`.`kalkulacija`$$

    CREATE DEFINER=`root`@`localhost` PROCEDURE `kalkulacija`()
    BEGIN
    update aos_products_cstm as t1
    set t1.kolicina_c=(select sum(t2.product_pri) from aos_products_quotes as t2 where t2.product_id=t1.id_c and t2.deleted=0 and pro_usluga_c='proizvod' group by t2.product_id) - (select sum(t2.product_rac) from aos_products_quotes as t2 where t2.product_id=t1.id_c and t2.deleted=0 and pro_usluga_c='proizvod' group by t2.product_id);
    END$$

    DELIMITER ;

    DELIMITER $$

    DROP TRIGGER /*!50032 IF EXISTS */ `lunasoft`.`racun_unos`$$

    CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `racun_unos` AFTER INSERT ON `aos_products_quotes`
    FOR EACH ROW BEGIN
    call kalkulacija;
    END;
    $$

    DELIMITER ;

    DELIMITER $$

    DROP TRIGGER /*!50032 IF EXISTS */ `lunasoft`.`racun_izmjena`$$

    CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `racun_izmjena` AFTER UPDATE ON `aos_products_quotes`
    FOR EACH ROW BEGIN
    call kalkulacija;
    END;
    $$

    DELIMITER ;

    DELIMITER $$

    DROP TRIGGER /*!50032 IF EXISTS */ `lunasoft`.`racun_brisanje`$$

    CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `racun_brisanje` AFTER DELETE ON `aos_products_quotes`
    FOR EACH ROW BEGIN
    call kalkulacija;
    END;
    $$

    DELIMITER ;

    Please help with this kolicina_c only be shown if both fields are filled and product_pri product_rac. I would like to make kolicina_c displayed if only one field filled. Thanks
  5. 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