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