on 2012 Nov 06 10:58 AM
CREATE OR REPLACE TRIGGER "dba".t_trace.trg_trace_insb BEFORE INSERT ON "dba".t_trace REFERENCING NEW AS new_t FOR EACH ROW BEGIN DECLARE @lProcName long varchar; DECLARE @lRevNo long varchar; IF EXISTS (SELECT x FROM sysprocedure a , new_t WHERE a.remarks IS NOT NULL AND a.remarks LIKE '*%' AND a.proc_name = SUBSTRING(new_t.trace_text, 0 , CASE WHEN CHARINDEX('(',new_t.trace_text) = 0 THEN LENGTH(new_t.trace_text) ELSE CHARINDEX('(',new_t.trace_text)-1 END) AND new_t.caller = 'CODE' AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED') ) THEN BEGIN SELECT a.proc_name ,REPLACE(a.remarks,'*','') INTO @lProcName ,@lRevNo FROM sysprocedure a ,new_t WHERE a.remarks IS NOT NULL AND a.remarks LIKE '*%' AND a.proc_name = SUBSTRING(new_t.trace_text, 0 , CASE WHEN CHARINDEX('(',new_t.trace_text) = 0 THEN LENGTH(new_t.trace_text) ELSE CHARINDEX('(',new_t.trace_text)-1 END) AND new_t.caller = 'CODE' AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED') --INSERT INTO dba.t_trace (trace_action,trace_text,caller) SELECT (trace_Action, [@ProcName +' ('+ @RevNo +')'], caller) FROM NEW_t UPDATE new_t SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ] END END IF END
When I run above code, I see erorr as could not execute statement. syntax error near "Update" on line 30 SQLCODE = -131, ODBC 3 State = '42000' Line 1, Column 1
Request clarification before answering.
Just a guess.... since it looks like you are using WATCOM SQL syntax, I would say that you are missing a semicolon at the end of this line:
AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED')and before the UPDATE.
You may want to also put a semicolon after this line:
SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ]and after the END and END IF and END lines.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks for the super quick reply. First time creating watcome triggers.I did that change and magic happened but I realized since its before insert trigger. I have commented --UPDATE new_t --SET new_t.trace_text = @lProcName +' ('+ @lRevNo +')' ; AND above that there is insert ststement that i need to include whihc i m using as INSERT INTO dba.t_trace (trace_action,trace_text,caller) SELECT trace_Action, @ProcName +' ('+ @RevNo +')', caller FROM new_t But during execution, I see, table new_t not found.why?
When using a row-level trigger (and before triggers are always row-level), you are not referencing a table (as in a statement-level trigger), but the "current record". So instead of using an UPDATE statement, you can simply access the column with a SET statement, somewhat like a local variable:
SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ];
I would recommend to check the docs for the difference between row- and statement-level triggers.
new_t is not a table when you use a "for each row" trigger, contrary to "for each statement" btw. "new_t.trace_action" is just a variable in this case, you don't need to add new_t to the from part to access the values just add "new_t." before the column name.
So in general, you can omit everything that handles "new_t" as a table in your trigger's code:
That (and possibly further adaptions) should get your code going.
What is the meaning of the INSERT statement? It would insert another entry in the same table (and that would trigger another insert recursively...). - In a BEFORE insert trigger, you usually only modify the values of the row to be inserted (or modify data in other tables).
User | Count |
---|---|
60 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.