Delete Before Insert Trigger Statement

• compgee

  SSC Rookie

  Points: 39

  More actions

  April 6, 2015 at 8:10 am

  #306463

  Need assistance with my trigger (my first – still learning so please be gentle).

  What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.

  What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database.

  so I can play with the data.

  Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).

  Again allot of explanations will help this beginner. Thank you. :hehe::hehe:

  Oh yeah this is on SQL Server 2008R2.

• Sean Lange

  SSC Guru

  Points: 286602

  More actions

  April 6, 2015 at 9:07 am

  #1788944

  Hi and welcome to the forums. In order to help we will need a few things:

  1. Sample DDL in the form of CREATE TABLE statements

  2. Sample data in the form of INSERT INTO statements

  3. Expected results based on the sample data

  Please take a few minutes and read the first article in my signature for best practices when posting questions.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Lowell

  SSC Guru

  Points: 323522

  More actions

  April 6, 2015 at 9:43 am

  #1788954

  deleting and inserting again where the your Id already exists is exactly what an update actually does...

  it sounds like if you cannot fix the applciation/datastream to know it has an id and should update or not, you wnat an instead of trigger for insert/update.

  as Sean said, we'd need specfici details to offer you a tested,coded solution.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!