RDBMS_PRACT_7_D



Assignment 7 Trigger : SET D

railway4=# create or replace function t1()returns trigger as '
begin
if new.a_time
raise exception ''correct time %'',new;
end if;
return new;
end '
language 'plpgsql';
CREATE FUNCTION

create trigger t2 before insert or update on train for each row execute  procedure t1();
CREATE TRIGGER

railway4=# insert into train values(105,'Kanyakumari express','20:00:00','18:00:00','aaa','xxx',12,60);
INSERT 0 1
railway4=# insert into train values(106,'Kanyakumari express','12:00:00','18:00:00','aaa','xxx',12,60);
ERROR:  arrival time 18:00:00 should be less than departure time

2)

railway4=# create trigger t8 before insert or update on ticket for each row execute procedure f1();
CREATE TRIGGER
railway4=# select * from ticket;
 tno | pid | ticket_no | b_no | no_berths |    date    | ticket_amt | status |     da    
-----+-----+-----------+------+-----------+------------+------------+--------+------------
 101 |  11 |         1 |   12 |         5 | 2009-05-03 |            |        |
 102 |  22 |         2 |   15 |         3 | 2009-04-02 |            |        |
 104 |  44 |         5 |   12 |         5 | 2010-05-03 |    4000.00 | w      |
 102 |  22 |         6 |   12 |         5 | 2010-03-03 |    4000.00 | w      |
 101 |  11 |         3 |   12 |         5 | 2010-05-03 |    2500.00 | w      |
 103 |  33 |         4 |   12 |         5 | 2010-05-03 |    2000.00 | c      | 2010-05-03
(6 rows)

railway4=# create or replace function f1() returns trigger as'
begin
if old.status!=new.status then
raise exception ''Cannot change status %'',old;
end if;
return old;
end
'
language 'plpgsql';
CREATE FUNCTION
railway4=# create trigger t before insert or update on ticket for each row execute procedure f1();
CREATE TRIGGER
railway4=# update ticket set status='c' where tno=104;
ERROR:  Cannot change status (104,44,5,12,5,2010-05-03,4000.00,w,)
railway4=# update ticket set status='w' where tno=104;
UPDATE 1

No comments:

Post a Comment