MsSQL Show Trigger problem
SHOW TRIGGERS FROM "aMSSQLdatabase";
program produces an error message when I wrote the code as follows;
/* SQL Error (156): Incorrect syntax near the keyword 'FROM'. */
To solve this problem, the following code should be executed;
exec sp_helptext 'aTriggerName';
When this solution, you can add a new update?
I noticed this thread and I have to report that to me this problem seems unfixed.
I'm using HeidiSQL v8.3.0.4713 against MSSQL 2008 R2 (tested also in 2005) and when I click on a trigger in the left treeview I have (always) this error:
date/time : 2014-02-19, 10:26:15, 433ms
computer name : PAOLO-ASUS
user name : paolo
registered owner : paolo / SoftEyE
operating system : Windows 8 x64 build 9200
system language : English
system up time : 2 days 13 hours
program up time : 7 seconds
processors : 8x Intel(R) Core(TM) i7-4700HQ CPU @ 2.40GHz
physical memory : 12479/16267 MB (free/total)
free disk space : (C:) 139,12 GB (D:) 590,45 GB
display mode : 1920x1080, 32 bit
process id : $28c0
allocated memory : 61,51 MB
largest free block : 131029,99 GB
executable : heidisql.exe
exec. date/time : 2014-02-19 10:15
version : 8.3.0.4713
compiled with : Delphi XE5
madExcept version : 4.0.8.1
callstack crc : $38576a79, $b27c5a45, $b27c5a45
exception number : 1
exception class : Unknown
exception message : Unknown.
main thread ($33f0):
00a6be53 heidisql.exe dbconnection 1753 +53 TAdoDBConnection.Query
7ff84b90 user32.dll GetMessageW
004ec526 heidisql.exe madExcept HandleException
004f9044 heidisql.exe madExcept InterceptAHandleExcept
00699b9e heidisql.exe Vcl.Controls TWinControl.MainWndProc
005da4b3 heidisql.exe System.Classes StdWndProc
7ff84b90 user32.dll DispatchMessageW
00812d8f heidisql.exe Vcl.Forms TApplication.ProcessMessage
00812e03 heidisql.exe Vcl.Forms TApplication.HandleMessage
008132ef heidisql.exe Vcl.Forms TApplication.Run
00c4611e heidisql.exe heidisql 76 +23 initialization
7ff84baf KERNEL32.DLL BaseThreadInitThunk
7ff84d1e ntdll.dll RtlUserThreadStart
I'm also a Delphi programmer so I will try to setup a Delphi environment to build the HeidiSQL project to be more specific about this error and, possibly, contribute to the project myself.
Thanks in advance, keep up the good work!
Paolo.
I told you that I replied to one of the solution is already old. I want to offer the same solution again. HeidiSQL is a to the "Microsoft SQL Server" when prompted with the following script to be accessed via a Trigger, the Trigger can be reached on the contents of;
exec sp_helptext ' aTriggerName ';
Heidi Version 9.3.0.5012 still has this bug.
Found new solution: SELECT OBJECT_DEFINITION(number of object_id);
will show the text of trigger.
SELECT *, SCHEMA_NAME("schema_id") AS 'schema' FROM "YOURDBNAME"."sys"."objects" WHERE "type" IN ('TR');
will show all object_id of triggers.
Heidi has had this error since I started using it. Today, I thought to open a bug report and here it is already :-)
Iām using 9.4.0.5125 and can confirm that the bug is still there. Heidi tries to show triggers by querying
SHOW TRIGGERS FROM "tablename";
To my knowledge, the most comprehensive way to get information about triggers for a specific table tablename in SQL Server is to use
SELECT
triggers.*
FROM
sys.triggers INNER JOIN sys.tables ON triggers.parent_id = tables.object_id
WHERE
tables.name = 'tablename'
ā¦ and if you would like to access the SQL definition of the trigger, use the following query:
SELECT OBJECT_DEFINITION (OBJECT_ID(N'dbo.trigger')) AS TriggerDefinition;
or ā if you already have the object_id
from sys.trigger
ā like this:
SELECT OBJECT_DEFINITION (123456789) AS TriggerDefinition;
Please login to leave a reply, or register at first.