TSQL _Trigger

  • Hi all

    I want to write a trigger for below scenario

    customer

    cust_idcust_namecust_dobcust_citystate_idcust_phonecust_emailcreated_dtmodified_dt

    1234Tim D2/2/1988Austin20048595098tim@gmail.com3/2/2011

    1235Bill P5/3/1980New York30046748898bill_p@gmail.com4/2/2011

    1278John Q6/13/1988Cheanni40084930393john@gmail.com5/5/2011

    1279John Black6/13/1984Cheanni40084930356john_b@gmail.com5/6/2011

    If there is no @ symbol exists in the email ID, reject that record

    customer age should be > 18

    when user inserted a record track the created dt as system date

    When any modication happens on a customer row, update the modified date based on the system date.

    any help.......

  • Insert Trigger

    ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser]

    ON [Inventory].[purchase_order_detail] AFTER INSERT

    AS

    SET NOCOUNT ON;

    Declare @TransDetail_Id Int

    Select @TransDetail_Id=purchase_order_detail_id from INSERTED

    IF TRIGGER_NESTLEVEL() > 1

    RETURN

    Update inventory.purchase_order_detail set [user_name]=(SELECT SYSTEM_USER), modify_user=(SELECT SYSTEM_USER),created_date=getdate(),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id

    Update Trigger

    ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser_update]

    ON [Inventory].[purchase_order_detail] AFTER UPDATE

    AS

    SET NOCOUNT ON;

    Declare @TransDetail_Id Int

    Select @TransDetail_Id=purchase_order_detail_id from INSERTED

    IF TRIGGER_NESTLEVEL() > 1

    RETURN

    Update inventory.purchase_order_detail set modify_user=(SELECT SYSTEM_USER),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id

    Regards

    Guru

  • Hi,

    Another way for adding created date when insert you can use default value.. Instead of passing arg/creating trigger

    Regards

    Guru

  • For email and age i think you can add constrain

  • hi

    Thanks to reply

  • GuruGPrasad (2/21/2012)


    Insert Trigger

    ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser]

    ON [Inventory].[purchase_order_detail] AFTER INSERT

    AS

    SET NOCOUNT ON;

    Declare @TransDetail_Id Int

    Select @TransDetail_Id=purchase_order_detail_id from INSERTED

    IF TRIGGER_NESTLEVEL() > 1

    RETURN

    Update inventory.purchase_order_detail set [user_name]=(SELECT SYSTEM_USER), modify_user=(SELECT SYSTEM_USER),created_date=getdate(),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id

    Update Trigger

    ALTER TRIGGER [Inventory].[purchase_order_detail_FillUser_update]

    ON [Inventory].[purchase_order_detail] AFTER UPDATE

    AS

    SET NOCOUNT ON;

    Declare @TransDetail_Id Int

    Select @TransDetail_Id=purchase_order_detail_id from INSERTED

    IF TRIGGER_NESTLEVEL() > 1

    RETURN

    Update inventory.purchase_order_detail set modify_user=(SELECT SYSTEM_USER),modify_date=getdate() where purchase_order_detail_id=@TransDetail_Id

    Regards

    Guru

    DO NOT DO THIS TYPE OF TRIGGER PROGRAMMING!!! This is a DEVASTATINGLY BAD DESIGN!!!

    You are putting the contents of INSERTED into a variable. What happens when INSERTED has more than ONE ROW?? You LOSE DATA!! I know of a company that went out of business because they based their logic on this type of code. You MUST code triggers to do fully set-based operations if you touch DELETED or INSERTED (or use cursors on them, G-d forbid).

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply