simple and useful triggers

1
(No Transcript)
2
simple and useful triggers
3
preview

• trigger fundamentals
• performance considerations
• samples
• copy deleted records to backup table
• create an audit change log
• duplicate inserts, updates and deletes to a
  second server
• implement client notification

4
trigger fundamentalswhat is a trigger?

• A trigger is a piece of code (similar to a
  stored procedure) that is executed on the server.
  Triggers differ from stored procedures because
  triggers are not called by the client, but
  instead are executed automatically in response to
  an event on the server.

5
trigger fundamentalstrigger summary

• requirements
• Advantage Server version 7 and greater ( 6.0 or
  newer client )
• Advantage Data Dictionary
• trigger types
• BEFORE
• INSTEAD OF
• AFTER
• trigger Events
• INSERT
• UPDATE
• DELETE

6
trigger fundamentalssupporting tables

• when a trigger is fired it contains state
  information, which can be used inside the body of
  the trigger.
• this information is stored in in-memory tables
• __new available when firing an INSERT or UPDATE
  trigger.
• __old available when firing an UPDATE or DELETE
  trigger.
• __error used to return an error code and/or error
  string from inside of a trigger.

7
continuedsupporting tables

• these tables can be referenced using just their
  base names (i.e. __new). For example, to read a
  value from the __new table the following SQL
  statement can be executed
• SELECT empid FROM __new
• contain one row with the same field definitions
  as the base table on which the update was
  performed

8
options

• no values
• Use if your trigger will not be making use of the
  __new or __old table
• no memos/blobs
• Use if your trigger will not be making use of
  memo or blob fields in the __new and __old tables
• priority
• If more than one trigger exists, a priority can
  be assigned to specify the firing order
• no transaction
• Disables implicit transactions

9
(No Transcript)
10
performance considerations
11
performance considerations

• implicit transactions
• performance vs. data integrity
• batch processes
• CREATE triggers after batch process
• 8.0 will have an option disable trigger
• options
• omit VALUES( __old and _new) tables
• omit memo and blob data

12
samples copy deleted records to backup table
13
samples copy deleted records to backup table

• Creation Script
• CREATE TRIGGER deletions_AfterDelete
• ON customers AFTER DELETE
• BEGIN
• INSERT INTO cust_bck SELECT FROM __old
• END
• PRIORITY 1
• English Creates Trigger that fires after a
  record has been deleted. The Trigger then fires
  an INSERT statement, which logs the values into
  the cust_bck table.

14
View/Application Share

• Live Meeting View/Application Share. Use Live
  Meeting gt Edit Slide Properties... to edit.
• Show Trigger creation in ARC.

15
samples create an audit change log
16
samples create an audit change log

• key code segments
• // open the __new and __old tables
• oNew.SQL.Text 'SELECT FROM __new'
• oNew.Open
• oOld.SQL.Text 'SELECT FROM __old'
• oOld.Open
• _______________
• // compare values between tables
• if CompareStr( oOld.FieldsiField.AsString,
  oNew.FieldsiField.AsString ) ltgt 0 then
• ..add to stringlist
• // insert into audit table
• oTable.Fields4.Value slChanges.Text

17
View/Application Share

• Live Meeting View/Application Share. Use Live
  Meeting gt Edit Slide Properties... to edit.
• Open AuditTrig and show creation in Delphi
• and show how to debug in local server mode
• linker tab include TD32 debug info
• include remote debug symbols

18
samples duplicate inserts, updates and deletes
to a second server
19
samples duplicate changes to a second server

• key code segments
• oConn.Name 'conn'
• oConnBackup.Name 'connBackup'
• oConnBackup.AdsServerTypes
  stADS_REMOTE
• oConnBackup.ConnectPath
  '\\bobthebuilder\c\data'
• oConnBackup.IsConnected TRUE
• oOld.SQL.Text 'SELECT FROM __old'
• oNew.SQL.Text 'SELECT FROM __new' ..

20
samples duplicate changes to a second server

• case ulEventType of
• ADS_TRIGEVENT_INSERT
• oBackup.SQL.Text 'SELECT FROM
  ' pcTableName ''
• oBackup.Open
• oBackup.append
• for iField 0 to (oNew.FieldCount
  -1) do
• oBackup.FieldsiField
  oNew.FieldsiField
• oBackup.Post
• end
• ADS_TRIGEVENT_UPDATE
• oBackup.SQL.Text 'SELECT FROM
  ' pcTableName
• ' WHERE CUST_NUM '
  oOld.Fields0.asstring ''
• oBackup.Open
• oBackup.Edit
• for iField 0 to (oNew.FieldCount
  -1) do
• oBackup.FieldsiField
  oNew.FieldsiField
• oBackup.Post

21
View/Application Share

• Live Meeting View/Application Share. Use Live
  Meeting gt Edit Slide Properties... to edit.

22
samples implement client notification
23
samples implement server notification

• key code segments
• oCommand.CommandText "SELECT user FROM
  system.iota"
• ' Add the trigger type to the send message
  (sMessage)
• Select Case ulEventType
• Case ADS_TRIGEVENT_UPDATE sMessage
  sMessage " Updated Record " ulRecNo.ToString
• Case ADS_TRIGEVENT_INSERT sMessage
  sMessage " Inserted Record "
  ulRecNo.ToString
• Case ADS_TRIGEVENT_DELETE sMessage
  sMessage " Deleted Record " ulRecNo.ToString
• End Select
• send message to client application.
• tcpClient.Connect("127.0.0.1", 8000)
• Dim networkStream As NetworkStream
  tcpClient.GetStream()
• Dim sendBytes As Byte() Encoding.ASCII.GetByte
  s(sMessage)
• networkStream.Write(sendBytes, 0,
  sendBytes.Length)

24
View/Application Share

• Live Meeting View/Application Share. Use Live
  Meeting gt Edit Slide Properties... to edit.

25
debugging

• using Advantage Local Server
• Open trigger project
• specify either a test application or ARC as host
  application for debugging
• set breakpoints in your trigger function
• run the debugger
• perform an operation that will cause the trigger
  to fire

26
continueddebugging

• using Advantage Database Server (remote server)
• open trigger project
• specify the Advantage server executable (ads.exe)
  as the host application for debugging
• set the command line parameters for the host
  application to -exe
• if the server is currently running, stop it
• set breakpoints in your trigger function
• run the debugger
• use your application or ARC to perform an
  operation that will cause the trigger to fire

27
questions??
28
Advantage Database Server The Official Guide
(ISBN 0-07-223084-3) is a new book, written by
Cary Jensen and Loy Anderson and published by
McGraw-Hill/Osborne Media Group, that
systematically guides a developer through key
functionality of Advantage and includes a
Companion CD with code samples and a single-user
license of Advantage Database Server version 7.0.
http//www.extendedsystems.com/ADS/ADSBookPromo.
htm