Thursday, 25 February 2016

Q)WRITE A TRIGGER STOP ALL DML OPERATIONS ON THURSDAYS?

STATEMENT LEVEL TRIGGER EXAMPLE:
Create or Replace Trigger mytrig
Before
delete or update or insert
on emp
declare
d varchar2(15);
Begin
Select rtrim(to_char(sysdate,'day')) into d from dual;
If (d='thursday') then Raise_Apllication_Error(-20000,'sorry we cannot perform any DML operations on emp because thursday is holiday');
End If;
End;

NOTE: In above program we use rtrim because by default the size of sysdate column is 9 bytes but here we are giving thursday,It has 8 bytes and 1 byte is space according to predefined size of sysdate column.
check in typing the following query in sql*plus 
    SELECT TO_CHAR(SYSDATE,'DAY')  FROM DUAL;
OUTPUT:   SYSDATE(DAY)
                    -  - - -  - -  -  -  -
                    Th u r  s d a y
 but in program we are using varchar2(15) out of 15 bytes of memory thursday takes only 8 bytes and remaining 7 bytes saved, so 9 bytes is not equal to 8 bytes.
 so for this reason we use rtrim in this query SELECT RTRIM(TO_CHAR(SYSDATE,'DAY')) INTO D FROM DUAL;                  

No comments:

Post a Comment

If you Like my blog Spread it and help friends for whom this blog is useful for their career.