BorlandTalk.com Borland discussion newsgroups   Archives   FAQ   Search   Memberlist   Usergroups   Register   Profile   Log in to check your private messages   Log in  Preventing a Trigger From Firing          BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) View previous topic :: View next topic   Author Message Bruce McIver Guest Posted: Fri Mar 18, 2005 3:26 pm    Post subject: Preventing a Trigger From Firing Hi, I'm using Delphi 7 and SQL Server 2000. I'm wondering whether, from Delphi, it is possible to prevent a SQL trigger from firing. Can I write some SQL to do this? I have an "on Delete" trigger which results in information being sent to an Audit table. However on a specific occasion, where I am deleting a large number of records at once, this is taking longer than I would like. Since it is not too important to send this info to the audit tables, if possible I would like to be able to stop the trigger from firing. Any help much appreciated. Bruce Back to top Wayne Niddery [TeamB] Guest Posted: Fri Mar 18, 2005 3:58 pm    Post subject: Re: Preventing a Trigger From Firing Bruce McIver wrote: Quote: Hi, I'm using Delphi 7 and SQL Server 2000. I'm wondering whether, from Delphi, it is possible to prevent a SQL trigger from firing. Can I write some SQL to do this? It is possible to do whatever the database, in this case, MSSQL, allows you to do. If there is a valid SQL statement in MSSQL you can execute to deactivate a trigger, then you can execute that from Delphi. Note that, if this is a multi-user system, this may pose a problem. In most cases, DDL statements are outside transaction control, so deactivating a trigger while you delete a batch of records will also mean all other users will be able to perform deletes without auditing taking place during that time. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html “It is error alone which needs the support of government. Truth can stand by itself.” - Thomas Jefferson Back to top Bruce McIver Guest Posted: Fri Mar 18, 2005 4:39 pm    Post subject: Re: Preventing a Trigger From Firing Thanks Wayne, Yep, I soon realised the answer to my question was very straightforward. i.e "Alter Table MyTable Disable Trigger tr_MyTable" Point taken about the problems with a multi-user system. Thanks. Bruce "Wayne Niddery [TeamB]" <wniddery (AT) chaffaci (DOT) on.ca> wrote Quote: Bruce McIver wrote: Hi, I'm using Delphi 7 and SQL Server 2000. I'm wondering whether, from Delphi, it is possible to prevent a SQL trigger from firing. Can I write some SQL to do this? It is possible to do whatever the database, in this case, MSSQL, allows you to do. If there is a valid SQL statement in MSSQL you can execute to deactivate a trigger, then you can execute that from Delphi. Note that, if this is a multi-user system, this may pose a problem. In most cases, DDL statements are outside transaction control, so deactivating a trigger while you delete a batch of records will also mean all other users will be able to perform deletes without auditing taking place during that time. -- Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com) RADBooks: http://www.logicfundamentals.com/RADBooks.html "It is error alone which needs the support of government. Truth can stand by itself." - Thomas Jefferson Back to top Thomas Steinmaurer Guest Posted: Fri Mar 18, 2005 10:46 pm    Post subject: Re: Preventing a Trigger From Firing Bruce, Quote: Yep, I soon realised the answer to my question was very straightforward. i.e "Alter Table MyTable Disable Trigger tr_MyTable" Point taken about the problems with a multi-user system. Thanks. If you don't want to deactivate the trigger entirely, then you could wrap the entire trigger bode in an IF clause, checking for a specific user running batch like operations. -- HTH, Thomas Steinmaurer LogManager Series - Logging/Auditing Suites supporting InterBase, Firebird, Advantage Database, MS SQL Server and NexusDB V2 (NEW!) Upscene Productions http://www.upscene.com Back to top Bruce McIver Guest Posted: Mon Mar 21, 2005 5:02 pm    Post subject: Re: Preventing a Trigger From Firing Thanks for that Thomas, Another problem I've run into concerns the permissions that the user requires before being allowed to execute the statement "Alter Table MyTable Disable Trigger tr_MyTable" I can get round this by assigning either the 'db_owner' or 'db_ddladmin' role to the appropriate users but this is not something I want to do. These users will have Select/Insert/Update/Delete permissions on these tables, but how do I grant them the rights to enable/disable a trigger on the tables? (without giving them 'db_owner' or 'db_ddladmin' rights) Is there another answer to this? Again, thanks in advance for any help. Bruce "Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam_ (AT) upscene (DOT) com> wrote in message news:423b5a30$1 (AT) newsgroups (DOT) borland.com... Quote: Bruce, Yep, I soon realised the answer to my question was very straightforward. i.e "Alter Table MyTable Disable Trigger tr_MyTable" Point taken about the problems with a multi-user system. Thanks. If you don't want to deactivate the trigger entirely, then you could wrap the entire trigger bode in an IF clause, checking for a specific user running batch like operations. -- HTH, Thomas Steinmaurer LogManager Series - Logging/Auditing Suites supporting InterBase, Firebird, Advantage Database, MS SQL Server and NexusDB V2 (NEW!) Upscene Productions http://www.upscene.com Back to top Thomas Steinmaurer Guest Posted: Mon Mar 21, 2005 7:09 pm    Post subject: Re: Preventing a Trigger From Firing Hi Bruce, Quote: Another problem I've run into concerns the permissions that the user requires before being allowed to execute the statement "Alter Table MyTable Disable Trigger tr_MyTable" I can get round this by assigning either the 'db_owner' or 'db_ddladmin' role to the appropriate users but this is not something I want to do. These users will have Select/Insert/Update/Delete permissions on these tables, but how do I grant them the rights to enable/disable a trigger on the tables? (without giving them 'db_owner' or 'db_ddladmin' rights) Is there another answer to this? AFAIK, one needs to be the owner of the underlying table, a member of the server role sysadmin or a member of the database roles db_owner or db_ddladmin. Another good reason not to go the route with disabling trigger, but enclosing the trigger body with an IF statement. IMHO -- HTH, Thomas Steinmaurer LogManager Series - Logging/Auditing Suites supporting InterBase, Firebird, Advantage Database, MS SQL Server and NexusDB V2 (NEW!) Upscene Productions http://www.upscene.com Back to top Dennis Passmore Guest Posted: Mon Mar 21, 2005 8:43 pm    Post subject: Re: Re: Preventing a Trigger From Firing Why not just create a general configuration table into which the user can add or delete data and have the trigger code query the configuration table to see if it is enabled or not. The trigger may always fire but not really do anything if it is disabled by data in the configuration table. Back to top Ray Marron Guest Posted: Tue Mar 22, 2005 9:59 pm    Post subject: Re: Re: Preventing a Trigger From Firing <Dennis Passmore> wrote Quote: Why not just create a general configuration table into which the user can add or delete data and have the trigger code query the configuration table to see if it is enabled or not. The trigger may always fire but not really do anything if it is disabled by data in the configuration table. Ooh! I don't currently have a problem like this, but I'm filing away this clever solution for a rainy day. Thanks! -- Ray Marron Back to top Bruce McIver Guest Posted: Tue Mar 29, 2005 10:27 am    Post subject: Re: Re: Preventing a Trigger From Firing Thank you Dennis. Consider it done! <Dennis Passmore> wrote Quote: Why not just create a general configuration table into which the user can add or delete data and have the trigger code query the configuration table to see if it is enabled or not. The trigger may always fire but not really do anything if it is disabled by data in the configuration table. Back to top Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First         BorlandTalk.com Forum Index -> Delphi Databases (SQL Servers) All times are GMT Page 1 of 1   Jump to: Select a forum C++ Builder Newsgroups----------------C++ BuilderC++ Builder (ActiveX)C++ Builder (Announcements)C++ Builder (Command Line Tools)C++ Builder DatabasesC++ Builder Databases (ADO)C++ Builder Databases (Desktop)C++ Builder Databases (InterBase Express)C++ Builder Databases (Multi-Tier)C++ Builder Databases (SQL Servers)C++ Builder (Graphics)C++ Builder (IDE)C++ Builder (Internet)C++ Builder (Internet Socket)C++ Builder (Internet Web)C++ Builder (Jobs)C++ Builder (Language)C++ Builder (Language C++)C++ Builder (Compatibility with MFC)C++ Builder (Native API)C++ Builder (Non-Technical)C++ Builder (Students)C++ Builder (Third Party Tools)C++ Builder (Upgrade)C++ Builder (VCL)C++ Builder (VCL Components Usage)C++ Builder (VCL Components Development)C++ Builder (VCL Usage)C++ Builder (WinAPI)C++ Builder Installation Delphi Newsgroups----------------Delphi (General)Delphi ActiveX Controls UsageDelphi ActiveX Controls DevelopmentDelphi As400Delphi DatabasesDelphi Databases (ADO)Delphi Databases (dbExpress)Delphi Databases (Desktop)Delphi Databases (InterBase Express)Delphi Databases (Multi-Tier)Delphi Databases (SQL Servers)Delphi DeploymentDelphi GraphicsDelphi IDEDelphi InternationalizationDelphi InternetDelphi Internet Isapi-WebBrokerDelphi Internet WebSnapDelphi Internet WinsockDelphi JobsDelphi Language BASMDelphi Language ObjectPascalDelphi Model Driven Architecture (General)Delphi Model Driven Architecture (Third Party)Delphi MultimediaDelphi Native APIDelphi Non-TechnicalDelphi ObjectPascalDelphi OLE AutomationDelphi OO designDelphi OpenToolsAPIDelphi Reporting-ChartingDelphi RTLDelphi StudentsDelphi TeamSourceDelphi Thirdparty ToolsDelphi Thirdparty Tools (General)Delphi Thirdparty Tools (IntraWeb)Delphi Thirdparty Tools (RaveReports)Delphi UpgradeDelphi VCLDelphi VCL ComponentsDelphi VCL Components UsingDelphi VCL Components WritingDelphi WebServices SOAPDelphi WinAPIDelphi XMLDelphi Installationcomp.lang.pascal.delphi.components.misccomp.lang.pascal.delphi.components.usagecomp.lang.pascal.delphi.components.writingcomp.lang.pascal.delphi.databasescomp.lang.pascal.delphi.misccomp.sources.delphi JBuilder Newsgroups----------------JBuilderJBuilder AnnouncementsJBuilder Applet IssuesJBuilder As400JBuilder CompilerJBuilder CORBA/RMIJBuilder DatabasesJBuilder DB SwingJBuilder DebuggerJBuilder DeploymentJBuilder DocumentationJBuilder EnterpriseJBuilder HandheldJBuilder IDEJBuilder JavaJBuilder JavaAPIJBuilder Java LanguageJBuilder Java SwingJBuilder Java BeansJBuilder Java Beans UsageJBuilder Java Beans DevelopmentJBuilder JBCLJBuilder JDataStoreJBuilder JobsJBuilder LinuxJBuilder MacintoshJBuilder & MobileSetJBuilder MultiLingual AppsJBuilder Non-TechnicalJBuilder OpenToolsJBuilder Servlets (JSP)JBuilder StudentsJBuilder Team DevelopmentJBuilder Thirdparty ToolsJBuilder UpgradeJBuilder Installation InterBase Newsgroups----------------InterBaseInterBase Bug DiscussionInterBase GeneralInterBase IBConsoleInterBase OpenSourceInterBase ReplicationInterBase SQLInterBase ThirdpartyInterBase Installation Miscellaneous Borland Newsgroups----------------AppCenterAppServerEnteraVisiBroker  You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum Powered by phpBB © 2001, 2006 phpBB Group SEO toolkit © 2004-2006 webmedic.