Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Stored Procedure

    I would like to make this a trigger for everytime the status is changed to TERMINATED. How would I go about making this a trigger. Is that possible?

    INSERT INTO TERMINATION
    ( Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
    SELECT STATUS, TM#, LASTNAME, FIRSTNAME, SSN#, HIREDATE, JOBTITLE
    FROM EmployeeGamingLicense
    WHERE (STATUS = N'TERMINATED')

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Something like

    Code:
    CREATE TRIGGER myTrigger99 ON EmployeeGamingLicense
    FOR UPDATE
    AS
    
    INSERT INTO TERMINATION(Status, [TM #], LastName, FirstName, SocialSecurityNumber, DateHired, Title)
         SELECT STATUS, [TM#], LASTNAME, FIRSTNAME, [SSN#], HIREDATE, JOBTITLE
           FROM inserted
          WHERE STATUS = 'TERMINATED'
    
    --Assuming you want them removed as well, and assumin SSN# is the key
    DELETE FROM EmployeeGamingLicense o
     WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Woohoooo

    ok Wonderful works like a charm that wasnt hard at all I can apply that to our corovan database too it keeps track of all files (records) that go to the storage facility. OH Great THANK YOU BRET THANK YOU VERY MUCH


    I am so glad my boss bought me sql server enterprise 2000

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Are you two going steady?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Jealous?

    But there are 2 T's in Brett

    and isn't it Heir Kaiser?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ya wol, mine furer!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Brettt, your code just has so much more charm than mine. I cannot compete.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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