Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
txghia58
Starting Member
4 Posts |
 Posted - 2006-02-07 : 22:54:17
|
I need to import a lot of data into a database so I need to disable all the constraints that are currently enabled and all the triggers that are currently enabled.I am having a problem getting a list of triggers that are enabled along with the table owner and the table name.Here is what I have so far. select "Table"=P.Name,"Trigger Name"=O.name, "Trigger Date"=O.refdate,"Defn"=CASE C.encrypted WHEN 0 THEN C.text ELSE '<< ENCRYPTED >>' ENDfrom syscomments C INNER JOIN sysobjects O ON (C.id = O.id)INNER JOIN sysobjects P ON (O.parent_obj = P.id)where O.xtype = 'TR' |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-07 : 23:06:47
|
quote:
I need to import a lot of data into a database so I need to disable all the constraints that are currently enabled and all the triggers that are currently enabled.
What are you going to do if the data vialates the constraints ?----------------------------------'KH' |
 |
|
|
txghia58
Starting Member
4 Posts |
Posted - 2006-02-07 : 23:36:46
|
| The data is being moved from a Production database to a testing database. The same triggers exist in the production database but are allways turned. I need the load to go quickly to minimize the time it takes to load the data from the production db as it will be in read only during this time. After the data is imported the production database can be brought online and then the triggers and constraints re-enabled. I realize that enableing the triggers and constraints is going to take time but that is time that the production database is back up and running. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-02-07 : 23:42:35
|
| Wouldn’t it be faster to backup Production DB and restore to Testing DB ?----------------------------------'KH' |
|
|
|
txghia58
Starting Member
4 Posts |
Posted - 2006-02-07 : 23:55:23
|
| There have been DDL changes to test that prevent this from happening efficiently. |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2006-02-07 : 23:59:33
|
| http://weblogs.sqlteam.com/davidm/archive/2004/02/27/999.aspxDavidMIntelligent Design is NOT science.A front-end is something that tries to violate a back-end. |
|
|
|
txghia58
Starting Member
4 Posts |
Posted - 2006-02-08 : 00:09:55
|
| Thank you taht got it for me and more. |
|
|
|
|
|
|
List of enabled triggers. Table and table owner