Suhaas Posted August 1, 2019 Report Share Posted August 1, 2019 2 hours ago, Spartan said: CREATE TRIGGER name_table ON name AFTER UPDATE,INSERT AS BEGIN INSERT INTO name_change_history( id, old_name, new_name, date_of_change ) SELECT i.id, d.name, i.name, GETDATE() FROM inserted i LEFT JOIN deleted d ON i.id = d.id WHERE i.name != ISNULL(d.name, -1) END; This should work Change “UPDATE” to “DELETE” in the above statement as per OP’s requirement Quote Link to comment Share on other sites More sharing options...
dasari Posted August 1, 2019 Report Share Posted August 1, 2019 3 hours ago, Sarvapindi said: Hmm..actually that table gets loaded from a file... How you are loading the file? Any etl job? one more thing are you using truncate table and load the file? If yes triggers might not work... you need to do something in etl job... Quote Link to comment Share on other sites More sharing options...
Sarvapindi Posted August 1, 2019 Author Report Share Posted August 1, 2019 5 minutes ago, dasari said: How you are loading the file? Any etl job? one more thing are you using truncate table and load the file? If yes triggers might not work... you need to do something in etl job... Yes.. thru azure df... truncate n load Quote Link to comment Share on other sites More sharing options...
Mr Mirchi Posted August 1, 2019 Report Share Posted August 1, 2019 3 hours ago, Sarvapindi said: Sql experts randi va..oka qn i have a table .. Table A... With 1000 records with columns below id. Name this table will be updated everyday...so some records will be deleted or some new records will be inserted. I want to find what are the records thats r inserted and records that r deleted everyday...and load those into another table... how to do very simple...go with trigger.....single trigger would do the job....i am doing in my proj Quote Link to comment Share on other sites More sharing options...
Mr Mirchi Posted August 1, 2019 Report Share Posted August 1, 2019 3 hours ago, Sarvapindi said: Hmm..actually that table gets loaded from a file... file ithe enti no issues....file ayina or back code numdi ayina insert or delete table meedhega trigger should do Quote Link to comment Share on other sites More sharing options...
dasari Posted August 1, 2019 Report Share Posted August 1, 2019 3 minutes ago, Sarvapindi said: Yes.. thru azure df... truncate n load R u using ssis? i am saying this instantly...not sure will it work for your problem... First load the new data in tempdb... Find the difference between your actual table and tempdb table ...move the differences to deleted table... Apply truncate load the tempdb table to actual table.. But truly one thing I recommend, find the difference between delete and truncate statement... Quote Link to comment Share on other sites More sharing options...
Sarvapindi Posted August 1, 2019 Author Report Share Posted August 1, 2019 9 minutes ago, Mr Mirchi said: file ithe enti no issues....file ayina or back code numdi ayina insert or delete table meedhega trigger should do We r not deleting from/inserting into table kada... data coming from file no Quote Link to comment Share on other sites More sharing options...
usetime63 Posted August 1, 2019 Report Share Posted August 1, 2019 read about slowly changing dimensions, hint, use merge for insert update. never use trigger . 1 Quote Link to comment Share on other sites More sharing options...
soodhilodaaram Posted August 1, 2019 Report Share Posted August 1, 2019 4 hours ago, Sarvapindi said: Azure datafactory.. did you create a surrogate key, you need to have a way to identify if a record is insert, delete or update best way is to output the file as parquet first which contains all the records, load from parquet to the actual tables, based on what you want to load into which tables this is a 2 step process Quote Link to comment Share on other sites More sharing options...
soodhilodaaram Posted August 1, 2019 Report Share Posted August 1, 2019 7 minutes ago, usetime63 said: read about slowly changing dimensions, hint, use merge for insert update. never use trigger . assuming him using azure data warehouse, inserts are pretty easy using polybase, updates are delete and inserts for better performance considerations I = Inserts U - Delete old rec and insert new records D - Delete Quote Link to comment Share on other sites More sharing options...
soodhilodaaram Posted August 1, 2019 Report Share Posted August 1, 2019 41 minutes ago, Sarvapindi said: We r not deleting from/inserting into table kada... data coming from file no this is about destination not source gootley Quote Link to comment Share on other sites More sharing options...
soodhilodaaram Posted August 1, 2019 Report Share Posted August 1, 2019 9 minutes ago, usetime63 said: read about slowly changing dimensions, hint, use merge for insert update. never use trigger . he can use trigger when using rubber Quote Link to comment Share on other sites More sharing options...
jajjanaka_jandri Posted August 1, 2019 Report Share Posted August 1, 2019 5 hours ago, Sarvapindi said: Sql experts randi va..oka qn i have a table .. Table A... With 1000 records with columns below id. Name this table will be updated everyday...so some records will be deleted or some new records will be inserted. I want to find what are the records thats r inserted and records that r deleted everyday...and load those into another table... how to do I think these two queries will suffice. Try and let me know. Insert into NewRecordsTable select * from ((SELECT id,name FROM TableA MINUS SELECT id,name FROM TableA_snapshot) union (SELECT id,name FROM TableA MINUS SELECT id,name FROM TableA_snapshot)); insert overwrite TableA_snapshot select * from TableA; Quote Link to comment Share on other sites More sharing options...
Quickgun_murugan Posted August 1, 2019 Report Share Posted August 1, 2019 6 hours ago, AndhraneedSCS said: It doesn't matter how the table gets populated. Trigger will fire up on insert/delete/update on your table,. All your trigger will do is populate this data into another table (call it dummy) and identify if it is an insert/update/delete and continue the operation on the main table. Your dummy table . will have all the information you may need and you just have to query it to see what inserts/updates/deletes ran on that table. if this is a mission-critical table that has 100s of transactions per second through out the day, this approach can cause performance bottleneck. Otherwise, it will work just fine. Triggers lo Magic tables access cheyyochu to track inserted and deleted records... Use them Quote Link to comment Share on other sites More sharing options...
reality Posted August 1, 2019 Report Share Posted August 1, 2019 Add load_indicator and Load_date columns to that table. Load indicator need to be populated with I, U, D... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.