CODEWITHSUNDEEP
sqloracle-databasetriggers
Shannon Hyland
Shannon Hyland

Reputation: 149

Creating a trigger

Im just wondering if someone could help me get started with creating a trigger.

I am needing to create a trigger that checks an INSERT statement for certain chars

eg: 

INSERT INTO table 
VALUES('ABC')

I want to check that the insert value has the A, then B, then C..

Any help would be awesome. Thanks

Upvotes: 1

Views: 242

Answers (3)

APC
APC

Reputation: 146349

The best way of doing this - best because it is the most performant, the easiest, the proper way of doing this - would be to use a check constraint.

For your original example that might look like this:

alter table t1
     add constraint t1_col1_ck check ( col1 = 'ABC')
;

The example you give in a comment won't work because you are applying a TO_NUMBER function to a string which is not numeric as it contains a colon. Is it supposed to be checking the time element of a DATE column? If so this could work:

alter table t1
     add constraint t1_starttime_ck 
     check ( (start_date - trunc(start_date)  < 0.5)
;

The precise details depend on the rules you want to enforce. The key thing is that integrity rules should be enforced through constraints not triggers whenever possible, and it is usually possible.

Upvotes: 3

CloudyMarble
CloudyMarble

Reputation: 37576

Check the Documentation for Creating Trigger, alternativly try this link. To check if certain substrings are included try the Instr function.

Upvotes: 0

Xophmeister
Xophmeister

Reputation: 9219

create or replace trigger iTableABC
  before insert
  on     table 
  for each row
begin
  if :new.value = 'ABC' then
    -- Do what it is you want here
  end if;
end;
/

This triggers before the insert, checking for the value 'ABC'. If you want it to trigger after the insert, make the appropriate changes. We assume that the table table has one field called value.

Upvotes: 2

Related Questions