1. SQL / Говнокод #8816

    −111

    1. 01
    2. 02
    3. 03
    4. 04
    5. 05
    6. 06
    7. 07
    8. 08
    9. 09
    10. 10
    11. 11
    12. 12
    13. 13
    14. 14
    15. 15
    16. 16
    17. 17
    18. 18
    19. 19
    20. 20
    21. 21
    22. 22
    23. 23
    24. 24
    25. 25
    26. 26
    27. 27
    28. 28
    29. 29
    30. 30
    31. 31
    32. 32
    33. 33
    34. 34
    35. 35
    36. 36
    37. 37
    38. 38
    39. 39
    40. 40
    41. 41
    42. 42
    43. 43
    44. 44
    45. 45
    46. 46
    47. 47
    48. 48
    49. 49
    50. 50
    51. 51
    52. 52
    53. 53
    54. 54
    55. 55
    56. 56
    57. 57
    58. 58
    59. 59
    60. 60
    61. 61
    62. 62
    63. 63
    64. 64
    65. 65
    66. 66
    67. 67
    68. 68
    69. 69
    70. 70
    71. 71
    72. 72
    73. 73
    74. 74
    75. 75
    76. 76
    77. 77
    78. 78
    79. 79
    80. 80
    81. 81
    82. 82
    83. 83
    84. 84
    85. 85
    86. 86
    87. 87
    88. 88
    89. 89
    90. 90
    91. 91
    92. 92
    93. 93
    94. 94
    95. 95
    96. 96
    97. 97
    98. 98
    CREATE TABLE "A" 
( "ID" NUMBER, 
"A" NUMBER, 
"B" VARCHAR2(20), 
CONSTRAINT "A_PK" PRIMARY KEY ("ID") ENABLE
)
/

CREATE OR REPLACE TRIGGER "A_2" 
AFTER
insert or update on "A"
for each row
begin

mut_table.bef_tr(:NEW.ID,:NEW.A);

end;
/
ALTER TRIGGER "A_2" ENABLE
/

CREATE OR REPLACE TRIGGER "A_3" 
AFTER
insert on "A"
declare
b number;

begin
mut_table.aft_tr;
end;
/
ALTER TRIGGER "A_3" ENABLE
/

CREATE OR REPLACE TRIGGER "A_AT" 
BEFORE
insert or update on "A"
for each row
declare 
v number;
PRAGMA AUTONOMOUS_TRANSACTION;
begin

:NEW.A:=:NEW.A+1;
:NEW.B:='!!!!!!!!!!';

commit;

end;
/
ALTER TRIGGER "A_AT" DISABLE
/

CREATE OR REPLACE TRIGGER "A_I_S" 
BEFORE
insert or update on "A"
begin
mut_table.set_initial_state;
end;
/
ALTER TRIGGER "A_I_S" ENABLE
/

CREATE OR REPLACE TRIGGER "A_T1" 
BEFORE
insert or update on "A"
for each row

begin

update A set 
A = :NEW.A+1,
B = 'was incremented'
where A = :NEW.A;
end;
/
ALTER TRIGGER "A_T1" DISABLE
/

CREATE OR REPLACE TRIGGER "BI_A" 
before insert on "A" 
for each row 
begin 
if :NEW."ID" is null then 
select "A_SEQ".nextval into :NEW."ID" from dual; 
end if; 
end; 

/
ALTER TRIGGER "BI_A" ENABLE
/


create or replace package MUT_TABLE as

procedure set_initial_state;
procedure bef_tr(ID number,A NUMBER);
procedure aft_tr;

    Запостил: kikis, 14 Декабря 2011

    Комментарии (2) RSS

    Добавить комментарий