disable constraint/triggers modify data and then enable them again.. is this fail proof?

25112 used Ask the Experts™
if you should disable trig/const and then remove/add data and then enable them, is that  a good idea that will work every time (as long the data is good)?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


logic in case:

exec sp_Msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
exec sp_Msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL';

truncate/delete tables.. insert new data.. and then...

exec sp_Msforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL';
Depends on what you call "fail". For example, if "fail proof against inserting the data that will violate the constraints", or "fail proof against enforcing the business logic implemented by the triggers", then it's exactly the opposite.
<is that  a good idea that will work every time (as long the data is good)?>

as workaround -1 time fix \cleanup\etc.- > it can be good --depends:
you may get this error when try to truncate table
"Cannot truncate table because it is being referenced by a FOREIGN KEY constraint"
As the perm solution as daily task\part of daily process- it is not the best--> check your DB design

However: it is up to you and your DB structure and functionalities...
trigger/const is something you want to perform on updation of any selected table.

if you want to do that functionality daily and if its not affecting result table while updating selected base table.

No need to write trigger/Const

and if you are performing once in a while and also taking care that result table data is not affecting and nothing voilate the data than no issue.

but its not preferable. bcs it might possible someone else operating same table at the same time using some other opration it will effect your data..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial