What authority non-DBAs need to create triggers

What authority would a non-DBA need to create a DB2 trigger? We are using Endevor and processors to create triggers. We were successful in creating stored procedures and thought triggers required similar authorization. 
CREATE TRIGGER ZJCTSS00.CASEU1               
  AFTER UPDATE OF                            
    CASE_YYMM_DT                             
  ON ZJCTSS00.CASE                           
  REFERENCING OLD AS OLDROW                  
              NEW AS NEWROW                  
  FOR EACH ROW MODE DB2SQL                   
  BEGIN ATOMIC                               
    UPDATE ZJCTSS00.NAMEX                    
    SET CASE_YYMM_DT = NEWROW.CASE_YYMM_DT   
    WHERE CASE_PROJ_NUM = OLDROW.CASE_NUM    
      AND SOURCE_IND    = 'R';               
END#                                    SQLCODE = -20100, ERROR:  AN
ERROR OCCURRED WHEN BINDING A TRIGGERED SQL STATEMENT.  INFORMATION
RETURNED: SEC

NUMBER : 2 SQLCODE -551, SQLSTATE 42501, AND MESSAGE TOKENS
ENDVID2,UPDATE,ZJCTSS00.NAMEX.CASE_YYMM_DT

SQLSTATE   = 56059 SQLSTATE RETURN CODE

SQLERRP    = DSNXODML SQL PROCEDURE DETECTING ERROR

    Requires Free Membership to View

    Login

Well, you'll need a bunch of authority to create a trigger. Creating a trigger requires that the user's privilege set include ALL of the following:

1. Either the CREATEIN privilege on the schema, SYSADM or SYSCTRL authority.

2. The TRIGGER privilege on the table. The privilege set must include at least one of the following: - The TRIGGER privilege on the table on which the trigger is defined
- The ALTER privilege on the table on which the trigger is defined
- DBADM authority on the database that contains the table
- SYSADM or SYSCTRL authority

3. The SELECT privilege on the table on which the trigger is defined if any transition variables or transition tables are specified.

4. The SELECT privilege on any table or view to which the search condition of triggered action refers.

5. The necessary privileges to invoke the triggered SQL statements in the triggered action.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in October 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top