ApexSQL Trigger Command Line Interface (CLI) switches

Applies to
ApexSQL Trigger

Summary
This article explains the ApexSQL Trigger CLI switches and their usage through the examples

Description

Connection switches

/serverSpecifies the server and instance name to connect to
Alias: /s
Format:/server:server_name[\instance_name]
Quick tip icon

Quick tip:

If a server is not specified the “(local)” instance is assumed.

/databaseSpecifies the name of the database to connect to
Alias: /d
Format:/database:AdventureWorks2014
/userSpecifies login for SQL Server authentication
Alias: /u
Format:/user:sa
Note:You must use this option together with the /password switch
Quick tip icon

Quick tip:

If a user is not specified a trusted connection is assumed

/passwordSpecifies 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

/archSpecifies the architecture file path
Alias: /a
Format:/arch:ApexSQLCustom_auditefile.audx
Note: If omitted ApexSQL Trigger architecture.audx will be used

Project file switches

/projectSpecifies the project file path
Alias: /pr
Format:/project:Audit_project.axap
Quick tip icon

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

/archinstallInstalls 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
/archremoveRemoves the auditing architecture
Alias: /ad
Format:/archremove

Trigger management switches

/addtriggerAdds triggers
Use [tableName1..tableNameN] to add triggers to all fields in tables listed
Alias: /at
Format:/addtrigger:TableName1:colName1.colName2…colNameN TableName2:colName1.colName2
/remtriggerRemoves 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 icon

Quick tip:

Table names in the /at, and the /rt switches are case sensitive.

/raddtriggerRe-add triggers
Use tableName1:colName1.colName2:…colNameN… tableNameN:colName1.colName2…colNameN:]
or only use [tableName1:…tableNameN:] for specific tables, columns and trigger types.
<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

/purgePurges audit data. Purge all data if no additional switches such as /maxrows and /older defined
Alias: /pg
Format:/purge
/olderPurges data older than the argument specified
Arguments:
d-days
m-months
y-years
Alias: /o
Format:/older:m3
/maxrowsRows 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

/outreportReport 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
/reportformatReport 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
/datefromStart 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
/datetoEnd 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
/rowsRows count
Alias: /r
Format:/rows:100
/tableTable 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
/applicationApplication 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”
/computerComputer 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
/filteruserUser 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
/dataDatabase 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
/actionAction 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

/areportGenerates aggregate report
Alias: /ar
Format:/areport
/aggcolumnsAggregate 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
/groupFilter 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

/sreportGenerates standard report
Alias: /sr
Format:/sreport
/oldvalueOld value filter definition. Use with the /sr switch
Alias: /ov
Format:/oldvalue
/newvalueNew value filter definition. Use with /sr switch
Alias: /nv
Format:/newvalue:5
/tablecolumnTable column filter definition. Use with the /sr switch
Alias: /tc
Format:/tablecolumn:name
/ownerOwner filter definition. Use with /sr switch
Alias: /ow
Format:/owner:name

DDL reporting switches

/ddlreportGenerates DDL report. Used with the /outreport switch
Alias: /ddlr
Format:/ddlreport
/ddloldvalueOld value filter definition. Use with the /ddlr switch
Alias: /ddlov
Format:/ ddloldvalue
/ddlnewvalueNew value filter definition. Use with /ddlr switch
Alias: /ddlnv
Format:/ddlnewvalue:5
/tablecolumnTable column filter definition. Use with the /sr switch
Alias: /tc
Format:/tablecolumn:name
/ownerOwner 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_scriptFull 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

/connectionfileUses the file that specifies connection properties.
Alias: /cf
Format:/connectionfile:conn.dat
/connectionfilesaveSaves 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