Database [PCS Infotech Placement]: Sample Questions 24 - 25 of 25

Get unlimited access to the best preparation resource for competitive exams : get questions, notes, tests, video lectures and more- for all subjects of your exam.

Question 24

Database
Edit

Statement

True-False▾

Lpad and Rpad have default pad character is space

Answer

TRUE

Explanation

The LPAD & RPAD
  • Lpad and Rpad SQL functions add padding characters to left or right side to given length.
  • The default padding character is a space.
  • String՚s with length greater than the required length will be trimmed.

Lpad

  • Function does left side pad of a string with a specific set of characters. Syntax:

    LPAD (string_to_pad, padded_length, [pad_string] )

  • string_to_pad is the string to pad characters to the left hand side.
  • padded_length is smaller than the original string.
  • If the parameter is absent, the LPAD function will pad space to the left side of string_to_pad.

Rpad

  • Function is used to pad the right side of a string with a specific set of characters. Syntax:

    RPAD (string_to_pad, padded_length, [pad_string] )

  • string_to_pad is the string to pad to the right hand side.
  • padded_length is smaller than the original string.
  • pad_string is the optional field.

Question 25

Database
Edit

Statement

True-False▾

Transaction function can be used in trigger

Answer

FALSE

Explanation

  • No, transaction function cannot be used in trigger. Triggers can՚t start transactions.
  • Triggers should not contain transaction control statements like commit, rollback, save point.
  • These are part of the execution of the triggering statement.
  • When the triggering statement is committed or rolled back, work in the trigger is committed or rolled back as well.

Trigger

  • Database trigger is special stored procedure running a specific action within a database.
  • Defined to run instead of or after DML action as INSERT, UPDATE and DELETE.
  • Event adding a record to a table, fires their execution.
  • Used for maintaining the integrity of the information on the database.

Commit inside trigger

  • Commit can be inside the trigger- but there is a catch
  • Make this trigger transaction be an independent transaction from its parent transaction using Pragma.
  • Pragma AUTONOMOUS_TRANSACTION builds the independent (child) transaction, started by another.
  • This should be in DECLARE section of any subprogram.
  • Can make modular and reusable blocks

Types of triggers:

  • Row Level Trigger
    • Gets executed before or after column value of row changes.
  • Column Level Trigger
    • Gets executed before or after the specified column changes.
  • For Each Row type
    • Executed for each row of the result set affected.
  • For Each Statement type
    • Executed only ones for the entire result set.
Types of Triggers

Advantages of Trigger

  • Alternative method for implementation referential integrity constraints.
  • Transactions are easy to store in database and can be applied consistently to the database.
  • It controls updates allowed in the database.
  • Adjust changes to entire database.
  • Used for calling stored procedures.

Developed by: