Post reply

Delete Trigger

  • December 10, 2006 at 5:51 pm

    #175185

    I need help with this trigger

    this is the destination table:

    create

    table HumanResources.JobCandidateHistory (

    JobCandidateID

    int not null unique,

    Resume

    xml ,

    Rating

    int default 5 not null,

    check(Rating between '1' and'10'),

    RejectedDate

    datetime not null,

    ContactID

    int,

    Foreign

    Key (ContactID) references Person.Contact(ContactID));

    and this is the trigger

    CREATE

    TRIGGER DJObCandidate

    ON

    HumanResources.JobCandidate

    FOR

    DELETE

    AS

    INSERT

    into HumanResources.JobCandidateHistory(

    JobCandidateID

    ,Resume,

    ModifiedDate

    )

    FROM

    HumanResources.JobCandidate

    please help!

  • December 10, 2006 at 9:50 pm

    #677142

    It's actually not clear what would you like to achieve with this trigger; Probably you look for:

    ...

    INSERT into HumanResources.JobCandidateHistory(

    JobCandidateID,Resume,

    ModifiedDate)

    select JobCandidateID,Resume, ModifiedDate FROM deleted

     

     

  • December 11, 2006 at 4:26 am

    #677161

    Actually what I understand U want to insert Deleted Records into History Table.

    For that your trigger query should be like this..

    INSERT into HumanResources.JobCandidateHistory(

    JobCandidateID

    ,Resume,

    ModifiedDate

    )

    SELECT JobCandidateID,Resume,ModifiedDate FROM

    Deleted

    There is two magic tables when trigger executes...

    INSERTED -- Keeps the data which u r going to insert.

    DELETED --Keeps data which U have deleted or Updated.

     

    And in SQL 2005 u can use OUTPUT.INSERTEDCOLNAME OR OUTPUT.DELETEDCOLNAME with insert or delete query. See Help for Insert or Delete.

     

  • December 12, 2006 at 7:34 am

    #677463

    Can you tell me where this term "magic" comes from??

  • December 14, 2006 at 12:08 am

    #678005

    Hi Ninja's RGR'us

    Trully I know none of it. But it [Magic or Psudo Table] is asked a lot of times in my Interviews. That's why I'm using it.

    And may be it comes from PowerBuilder - DataWindow or Sybase db or Because of Inserted and Deleted tables are maintained by SQL itself that's why. But exactly I donot know from where this term comes.

    Please It will be good to know for information if U tell me.

    Thanks

     

  • December 14, 2006 at 9:28 am

    #678127

    Thank you guys, I got it to work.

     

    Thanks again!

  • December 14, 2006 at 9:38 am

    #678130

    I just rememeber one post where Joe Celko flamed someone for using that term, and now I can see it is more spread than I first imagined.  Now I can understand a little more why.

  • December 14, 2006 at 9:03 pm

    #678250

    Hi Ninja's

    Joe Celko The SQL Apprentice

    Rows are not records; fields are not columns; tables are not files!!

    Move over Donald Trump and Martha Stewart, this is geek apprentice heaven!

    quote

    		Hi all how to create magic table name? magic tables will created automatically when trigger fires, i want to know that naming convention means tablename_insert like that, i want correct forma...

    Okay, you missed the basic idea of an RDBMS.  A table is an entity or a relationship in a data model.  This data model is based on an external reality and it is consistent with that reality. 

    We do not work and live in FairyLand where elephants drop out of the sky by magic.  Please stop programming befoer you hurt someone and take the time to read at least one book on RDBMS and data modeling.

    But Still I'm not aware from where this Magic Term appear in SQL Server Word. But this concept is already exists in PowerBuilder-DataWindow.

    Thanks

  • December 15, 2006 at 6:34 am

    #678308

    Yup that's the one.  Thanx for the research .

Viewing 9 posts - 1 through 8 (of 8 total)

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