Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Triggers |
Sat, Oct 24 2009 8:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Possibly a "what sort of cigarette" is that question. Just testing out my reverse engineered db creation script with log messages I noticed that the longest operation was the creation of the triggers and knowing there's a wadge of surplus white space and CRLSs I wondered is it anything like HTML where getting rid of the excess white space etc would make them faster?
Roy Lambert |
Sat, Oct 24 2009 9:20 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Possibly a "what sort of cigarette" is that question. Just testing out my reverse engineered db creation script with log messages I noticed that the longest operation was the creation of the triggers and knowing there's a wadge of surplus white space and CRLSs I wondered is it anything like HTML where getting rid of the excess white space etc would make them faster? >> You can try it, but I doubt that it will bring you much extra performance. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Oct 24 2009 9:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>You can try it, but I doubt that it will bring you much extra performance. What do you reckon the scales will do weighing legibility Vs extra performance? Roy Lambert |
Tue, Oct 27 2009 7:16 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< What do you reckon the scales will do weighing legibility Vs extra performance? >> I'd personally go for legibility. Not being able to read your own triggers isn't really worth a few extra milliseconds. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Oct 27 2009 7:42 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>I'd personally go for legibility. Not being able to read your own triggers >isn't really worth a few extra milliseconds. Me too most of the time, but when that few extra milliseconds builds up by multiplying by a few thousand or more I start to wonder. I'm over the moon about the DISABLE TRIGGER functionality (it would have been over the sun if there was a global one). With my audit log triggers enabled a block operation (eg import) got bombed because it was taking to long. Disable the triggers and it went down to a few seconds. Brilliant, and with a utility program (just pass in the name of the table) not a lot or work. Roy Lambert |
Tue, Oct 27 2009 8:13 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I'm over the moon about the DISABLE TRIGGER functionality (it would have been over the sun if there was a global one). >> A global what ? DISABLE TRIGGER statement ? << With my audit log triggers enabled a block operation (eg import) got bombed because it was taking to long. Disable the triggers and it went down to a few seconds. Brilliant, and with a utility program (just pass in the name of the table) not a lot or work. >> Cool. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 28 2009 4:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>A global what ? DISABLE TRIGGER statement ? You guessed My suggested syntax DISABLE ALL TRIGGERS FOR TABLE DISABLE ALL TRIGGERS FOR DATABASE JUST DISABLE EVERYTHING ENABLE ALL TRIGGERS FOR TABLE ENABLEALL TRIGGERS FOR DATABASE WOOPS SHOULDN'T HAVE DONE THAT Roy Lambert |
Wed, Oct 28 2009 11:14 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< You guessed My suggested syntax >> I'll see what I can do, but this stored procedure will do what you want. Just pass NULL or '' in as the TableName to disable all triggers in the database. CREATE PROCEDURE DisableTriggers(TableName VARCHAR) BEGIN DECLARE TempCursor CURSOR FOR TempStatement; DECLARE TempTableName VARCHAR; DECLARE TempTriggerName VARCHAR; IF (COALESCE(TableName,'') <> '') THEN PREPARE TempStatement FROM 'SELECT * FROM Information.Triggers WHERE TableName=?'; OPEN TempCursor USING TableName; FETCH FIRST FROM TempCursor (Name) INTO TempTriggerName; WHILE (NOT EOF(TempCursor)) DO EXECUTE IMMEDIATE 'DISABLE TRIGGER '+TempTriggerName+' ON '+TableName; FETCH NEXT FROM TempCursor (Name) INTO TempTriggerName; END WHILE; ELSE PREPARE TempStatement FROM 'SELECT * FROM Information.Triggers'; OPEN TempCursor USING TableName; FETCH FIRST FROM TempCursor (TableName, Name) INTO TempTableName, TempTriggerName; WHILE (NOT EOF(TempCursor)) DO EXECUTE IMMEDIATE 'DISABLE TRIGGER '+TempTriggerName+' ON '+TempTableName; FETCH NEXT FROM TempCursor (TableName, Name) INTO TempTableName, TempTriggerName; END WHILE; END IF; END -- Tim Young Elevate Software www.elevatesoft.com Attachments: disablealltriggers.SQL |
Wed, Oct 28 2009 11:38 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
I see, skim reading posts again eh. You missed "and with a utility program (just pass in the name of the table)" <vbg> Roy Lambert |
Wed, Oct 28 2009 1:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< I see, skim reading posts again eh. You missed "and with a utility program (just pass in the name of the table)" <vbg> >> Yep, I missed that. But, it's still useful to have out there for others. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |