Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    15

    Unanswered: Help with Trigger

    Hi ya,

    I have a question, I've never written a trigger before. BOL isn't that much help for what I want to do.

    I have 1 table:

    TABLE

    STATUS, COMPLETEDATE,LASTUPDATE

    I would like to set "CompleteDate" to the "LastUpdate" value when ever the value of "Status" is set to closed.

    Running SQL 2000, what's the best way to write this?

    Ken

  2. #2
    Join Date
    May 2003
    Location
    Epsom, United Kingdom
    Posts
    42

    Re: Help with Trigger

    -- CREATE SOME SAMPLE DATA

    SET DATEFORMAT DMY

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MYTABLE')
    DROP TABLE MYTABLE

    CREATE TABLE MYTABLE (
    STATUS CHAR(7),
    COMPLETEDATE DATETIME,
    LASTUPDATE DATETIME)
    GO

    INSERT INTO MYTABLE(STATUS, COMPLETEDATE, LASTUPDATE)
    SELECT 'UNKNOWN', CONVERT(DATETIME, '23/08/1978'), GETDATE()
    UNION ALL
    SELECT 'OPEN', CONVERT(DATETIME, '29/07/2003'), GETDATE()-6000
    UNION ALL
    SELECT 'OPEN', CONVERT(DATETIME, '01/01/3000'), GETDATE() - 500


    -- CREATE THE TRIGGER

    IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME = N'MYTRIGGER' AND TYPE = 'TR')
    DROP TRIGGER MYTRIGGER
    GO

    CREATE TRIGGER MYTRIGGER
    ON MYTABLE
    FOR UPDATE
    AS
    BEGIN
    UPDATE MYTABLE
    SET COMPLETEDATE = LASTUPDATE
    WHERE STATUS = 'CLOSED'
    END
    GO

    -- BEFORE TRIGGER

    SELECT * FROM MYTABLE

    -- UPDATE ONE RECORD

    UPDATE MYTABLE
    SET STATUS = 'CLOSED'
    WHERE STATUS = 'UNKNOWN'

    -- AFTER TRIGGER

    SELECT * FROM MYTABLE

    BOL provides enough information to know how to use such command. You just have to read it!
    Last edited by Crespo-n00b; 07-29-03 at 13:25.
    Shadow to Light

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •