Q

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

Dig deeper on IBM DB2 management

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchAWS

SearchContentManagement

SearchOracle

SearchSAP

SearchSOA

SearchSQLServer

Close