What type of trigger do you create a for each row

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: mation into the AUDIT_TABLE. What type of trigger do you create? A. FOR EACH ROW trigger on the EMP table. B. Statement-level trigger on the EMP table. C. FOR EACH ROW trigger on the AUDIT_TABLE table. D. Statement-level trigger on the AUDIT_TABLE table. E. FOR EACH ROW statement-level trigger on the EMPtable. www.ExamMagic.com 1Z0-147 Answer: A Explanation: A FOR EACH ROW trigger on the EMP table will fire for each row that is modified in the employee table and will insert a record in the AUDIT_TABLE for each corresponding row modified in the EMP Table. A Statement-level trigger will only fire once and could only be used to insert a single row into the AUTIT_TABLE. Incorrect Answers B: Would only insert one row into the AUDIT_TABLE and could not reference the :OLD &: NEW qualifies. Therefore you could not track the changes made to the EMP Columns, you could only log that an update was performed on the table. C: The trigger should be based on the UPDATE Event of the EMP Table. D: Incorrect Trigger Type on the wrong table E: Incorrect Trigger Type WWW.ExamMagic.COM - 13: Which statements are true? (Choose all that apply) A. If errors occur during the compilation of a trigger, the trigger is still created. B. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors. C. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors. D. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors. Answer: A, C, D Explanation: A: If there is a compilation error in the CREATE TRIGGER statement, the trigger is still created. C: You may view the compilation errors in SQL*Plus by issuing the SHOW ERRORS command. D: The TEXT column of the USER_ERRORS View contains the compilation errors. The LINE Column stored the LINE number of the error and the POSITION column contains the character POSITION of the LINE identified in the line COLUMN. WWW.ExamMagic.COM - 14: www.ExamMagic.com 1Z0-147 Which two dictionary views track dependencies? (Choose two) A. USER_SOURCE B. UTL_DEPTREE C. USER_OBJECTS D. DEPTREE_TEMPTAB E. USER_DEPENDENCIES F. DBA_DEPENDENT_OBJECTS Answer: D, E Explanation: D: DEPTREE_TEMPTAB is a temporary table used to store dependency information returned by the DEPTREE_FILL procedure. E: USER_DEPENDECIES is used to display direct dependencies. ALL _DEPENDENCIES and DBA_DEPENDENCIES also store dependency information. Incorrect answers A: USER_SOURCE describes the text source of the stored objects owned by the current user B: UTL_DEPTREE is not a valid data dictionary view. C: USER_OBJECTS contains basic information about all objects owned by the current user, but does not contain dependency information F: DBA_DEPENDENT_OBJECTS this is not a valid data dictionary view however there is a DBA_DEPENDENCIES view WWW.ExamMagic.COM - 15: Given a function CALCTAX: CREATE OR REPLACE FUNCTION calctax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true? A. You need to execute the command CALCTAX(1000);. B. You need to execute the command EXECUTE FUNCTION calctax; C. You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000);. D. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX; E. You need to create a SQL *Plus environment variable X and issue the command www.ExamMagic.com 1Z0-147 EXECUTE :X := CALCTAX(1000); Answer: E When you call a function from SQL*PLUS you need to assign the returned value a bind variable, and you need the EXECUTE command to execute the function. WWW.ExamMagic.COM - 16: What happens during the execute phase with dynamic SQL for INSERT, UPDATE, and DELETE operations? A. The rows are selected and ordered. B. The validity of the SQL statement is established. C. An area of memory is established to process the SQL statement. D. The SQL statement is run and the number of rows processed is returned. E. The area of memory established to process the SQL statement is released. Answer: D All SQL statements have to go through various stages. Some stages may be skipped. 1. Parse Every SQL statement must be parsed. Parsing the statement includes checking the statement's syntax and validating the statement, ensuring that all references to objects are correct, and ensuring that the relevant privileges to those objects exist. 2. Bind After parsing, the Oracle server knows the meaning of the Oracle statement but still may not have enough information to execute the statement. The Oracle server may need values for any bind variable in the statement. The process of obtaining these values is called binding variables. 3. Execute At this point, the Oracle server has all necessary information and resources, and th...
View Full Document

Ask a homework question - tutors are online