Applies to
ApexSQL Trigger
Summary
This article explains the ApexSQL Trigger CLI switches and their usage through the examples
Description
Connection switches
| /server | Specifies the server and instance name to connect to Alias: /s Format:/server:server_name[\instance_name] |
|
Quick tip: If a server is not specified the “(local)” instance is assumed. |
| /database | Specifies the name of the database to connect to Alias: /d Format:/database:AdventureWorks2014 |
| /user | Specifies login for SQL Server authentication Alias: /u Format:/user:sa Note:You must use this option together with the /password switch |
|
Quick tip: If a user is not specified a trusted connection is assumed |
| /password | Specifies login’s password when using SQL Server authentication Alias: /p Format:/password:123password Note:You must use this option together with the /user switch |
Architecture file switches
| /arch | Specifies the architecture file path Alias: /a Format:/arch:ApexSQLCustom_auditefile.audx Note: If omitted ApexSQL Trigger architecture.audx will be used |
Project file switches
| /project | Specifies the project file path Alias: /pr Format:/project:Audit_project.axap |
|
Quick tip: If a project or architecture file is saved outside of the installation directory, specify the path where it’s located. The full path of the project or architecture file, along with the file extension, must be defined, so that command could be correct. |
Architecture management switches
| /archinstall | Installs architecture. Arguments: [d]-data tables structure [g]-aggregate report stored procedure [s]-standard report stored procedure [dr]-ddl report stored procedure [v]-audit view [p]-purge data stored procedure [u]-undo stored procedure [r]-delete architecture stored procedure [z]-analyze stored procedure [a]-Install entire architecture Arguments can be combined Alias: /ai Format:/ai:d g s |
| /archremove | Removes the auditing architecture Alias: /ad Format:/archremove |
Trigger management switches
| /addtrigger | Adds triggers Use [tableName1..tableNameN] to add triggers to all fields in tables listed Alias: /at Format:/addtrigger:TableName1:colName1.colName2…colNameN TableName2:colName1.colName2 |
| /remtrigger | Removes triggers Use [tableName1:i.u.d [insert [i] | update [u] | delete [d]] …tableNameN:i.u.d [insert [i] | update [u] | delete [d]] format to specify table and trigger type or [tableName1..tableNameN] for specific tables and all triggers types Alias: /rt Format:/remtrigger:TableName1:i.u.d TableName2:i.u |
|
Quick tip: Table names in the /at, and the /rt switches are case sensitive. |
| /raddtrigger | Re-add triggers Use tableName1:colName1.colName2: or only use [tableName1: <TriggerType> is a combination of [insert [i] | update [u] |delete [d]] triggers separated with a dot (e.gi.u.d). All triggers are re-added if the parameters are omitted Alias: /rat Format:/rat: TableName1:colName1.colName2…colNameN TableName2:colName1.colName2 |
Purge data switches
| /purge | Purges audit data. Purge all data if no additional switches such as /maxrows and /older defined Alias: /pg Format:/purge |
| /older | Purges data older than the argument specified Arguments: d-days m-months y-years Alias: /o Format:/older:m3 |
| /maxrows | Rows count Arguments: /pg-max rows in audit table, older deleted /ar-number of rows in aggregate report /sr-number of rows in standard report Alias: /mr Format:/maxrows:100 |
Reporting switches
Reporting filter switches
| /outreport | Report output filename. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /or Format:/outreport:report.csv |
| /reportformat | Report output file format. Use it with the /ar switch for aggregate report, with the /sr switch for standard report or with the /ddlr switch for ddl report Alias: /rf Format:/reportformat:pdf |
| /datefrom | Start of the time range. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /df Format:/datefrom:20140105 |
| /dateto | End of the time range. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /dt Format:/dateto:20140510 |
| /rows | Rows count Alias: /r Format:/rows:100 |
| /table | Table name filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /tb Format:/table:Contact |
| /application | Application filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /app Format:/application:”Microsoft SQL Management Studio” |
| /computer | Computer filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /comp Format:/computer:computer_name |
| /filteruser | User filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /fu Format:/filteruser:user_name |
| /data | Database filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Alias: /db Format:/data:database_name |
| /action | Action filter definition. Use it with the /ar switch for aggregate report or with the /sr switch for standard report Arguments: i-insert u-update d-delete crt-create alt-alter drp-drop Arguments can be combined Alias: /act Format:/action:i |
Aggregate reporting switches
| /areport | Generates aggregate report Alias: /ar Format:/areport |
| /aggcolumns | Aggregate report columns selection. Use it with the /ar switch Arguments: t-table name m-modified by o-action p-application c-computer Arguments may be combined Alias: /ac Format:/aggcolumns:tmo |
| /group | Filter grouping option for aggregate report. Use it with the /ar switch Group by: h-hour d-days m-months y-years Alias: /gr Format:/group:d |
Standard reporting switches
| /sreport | Generates standard report Alias: /sr Format:/sreport |
| /oldvalue | Old value filter definition. Use with the /sr switch Alias: /ov Format:/oldvalue |
| /newvalue | New value filter definition. Use with /sr switch Alias: /nv Format:/newvalue:5 |
| /tablecolumn | Table column filter definition. Use with the /sr switch Alias: /tc Format:/tablecolumn:name |
| /owner | Owner filter definition. Use with /sr switch Alias: /ow Format:/owner:name |
DDL reporting switches
| /ddlreport | Generates DDL report. Used with the /outreport switch Alias: /ddlr Format:/ddlreport |
| /ddloldvalue | Old value filter definition. Use with the /ddlr switch Alias: /ddlov Format:/ ddloldvalue |
| /ddlnewvalue | New value filter definition. Use with /ddlr switch Alias: /ddlnv Format:/ddlnewvalue:5 |
| /tablecolumn | Table column filter definition. Use with the /sr switch Alias: /tc Format:/tablecolumn:name |
| /owner | Owner filter definition. Use with /sr switch Alias: /ow Format:/owner:name |
Include utility files switches
| /include_ utility_files |
Include utility files Alias: /iuf Format:/include_utility_files: path to the utility files Note: If omitted %LOCALAPPDATA%\Local\ApexSQL\ApexSQLTrigger\ApexSQL trigger.inc will be used |
Output file options
| /output_script | Full path to save a script that is used for creating triggers (with corresponding extension “.sql”) Alias: /os Format:/output_script:output path |
ApexSQL Trigger Viewer switches
ApexSQL Trigger Viewer uses the following groups of switches that are used in ApexSQL Trigger: Connection switches, Architecture file switches, Reporting switches (including Reporting filter, Standard and Aggregate) and Additional switches.
Exclusive switches for ApexSQL Trigger Viewer are:
Connection file switches
| /connectionfile | Uses the file that specifies connection properties. Alias: /cf Format:/connectionfile:conn.dat |
| /connectionfilesave | Saves the file that specifies connection properties. Alias: /cfs Format:/connectionfilesave:conn.dat |
For the list and detailed explanation of the common switches please refer to this article
Examples
Note: All examples assume that the current directory is the application directory. If not, the full path to ApexSQLTrigger.com or ApexSQLTriggerViewer.com must be specified.
Install all architecture types on the AdventureWorks database. No server is specified, so “(local)” is used:
ApexSQLTrigger.com /d:AdventureWorks /ai
Create triggers using default architecture ApexSQL Trigger architecture.audx on the AdventureWorks database:
ApexSQLTrigger.com /d:AdventureWorks /at
Purge data older than 5 hours from the AdventureWorks database:
ApexSQLTrigger.com /d:AdventureWorks /pg /o:h5
Create an aggregate report for database AdventureWorks with name “report.csv“, in .csv file format, in the current folder. The existing file of the same name will not be overwritten. To overwrite the file, add /f
ApexSQLTrigger.com /d:AdventureWorks /or:report.csv /rf:csv /ar
Create an aggregate report for all audit data with default output report.csv:
ApexSQLTrigger.com /d:AdventureWorks /ar
Create standard report for all audit data, with name “report1.xls“. The existing file of the same name will not be overwritten. To overwrite the file, add /f
ApexSQLTrigger.com /d:AdventureWorks /or:report1.xls /rf:xls /sr
Create PDF report from the time period: 2004 11 23 to 2006 11 23:
ApexSQLTrigger.com /rf:pdf /df:20041123 /dt:20061123
Delete more than 5 rows in log:
ApexSQLTrigger.com /d:AdventureWorks /pg /mr:5
Installing data table structure and both report types:
ApexSQLTrigger.com /d:AdventureWorks /ai:dgs
Delete audit data except last 2000 transactions:
ApexSQLTrigger.com /d:AdventureWorks /pg /mr:2000
Get aggregate report in PDF format, in standard output “report.pdf“, with columns:table name, action, modified by:
ApexSQLTrigger.com /d:AdventureWorks /rf:pdf /ar /ac:tmo
Get aggregate report in PDF format, in standard output “report.pdf“, only for delete operation:
ApexSQLTrigger.com /d:AdventureWorks /rf:pdf /ar /act:d
Get aggregate report in PDF format, in standard output “report.pdf” only for delete operation with connect using connection file conn.dat:
ApexSQLTriggerViewer.com /cf:conn.dat /rf:pdf /ar /act:d
Get standard report in excel format, in standard output “report.xls” only for delete operation with connect using connection file conn.dat:
ApexSQLTriggerViewer.com /cf:conn.dat /rf:xls /sr /act:d
