Sql Triggers with some examples

Table creation:

create table general_comments (
 comment_id  integer primary key,
 on_what_id  integer not null,
 on_which_table  varchar(50),
 user_id   not null references users,
 comment_date  date not null,
 ip_address  varchar(50) not null,
 modified_date date not null,
 content   clob,
-- is the content in HTML or plain text (the default)
 html_p   char(1) default 'f' check(html_p in ('t','f')),
 approved_p  char(1) default 't' check(approved_p in ('t','f'))
);


Trigger Creation:

create trigger general_comments_modified
before insert or update on general_comments
for each row
begin
 :new.modified_date := sysdate;
end;



Another One Type Of Trigger:


create table queries (
 query_id integer primary key,
 query_name varchar(100) not null,
 query_owner not null references users,
 definition_time date not null,
 -- if this is non-null,
 we just forget about all the query_columns
 -- stuff; the user has hand edited the SQL
 query_sql varchar(4000)
);

create table queries_audit (
 query_id integer not null,
 audit_time date not null,
 query_sql varchar(4000)
);



create or replace trigger queries_audit_sql
before update on queries
for each row
when (old.query_sql is not null and (new.query_sql is null
or old.query_sql <> new.query_sql))
begin
insert into queries_audit (query_id, audit_time, query_sql)
values
(:old.query_id, sysdate, :old.query_sql);
end;

Bulk insert in C#

DataTable dt = new DataTable();

string line = null;

int i = 0;

using (StreamReader sr = File.OpenText(@"c:\temp\table1.csv"))

{

while ((line = sr.ReadLine()) != null)

{

string[] data = line.Split(',');

if (data.Length > 0)

{

if (i == 0)

{

foreach (var item in data)

{

dt.Columns.Add(new DataColumn());

}

i++;

}

DataRow row = dt.NewRow();

row.ItemArray = data;

dt.Rows.Add(row);

}

}

}