schema/spacewalk/postgres/triggers/rhnChannel.sql | 42 ++++------
schema/spacewalk/postgres/triggers/rhnChannelComps.sql | 10 ++
schema/spacewalk/postgres/triggers/rhnConfigChannel.sql | 17 +---
schema/spacewalk/postgres/triggers/rhnConfigRevision.sql | 17 +---
schema/spacewalk/postgres/triggers/rhnErrataPackage.sql | 4
schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql | 17 +++-
schema/spacewalk/postgres/triggers/rhnKickstartSession.sql | 18 +++-
schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql | 18 ++--
schema/spacewalk/postgres/triggers/rhnPackage.sql | 20 +++-
schema/spacewalk/postgres/triggers/rhnServerAction.sql | 2
schema/spacewalk/postgres/triggers/rhnServerGroup.sql | 21 +----
schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql | 1
schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql | 4
schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql | 2
schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql | 2
schema/spacewalk/postgres/triggers/rhn_contact_methods.sql | 14 ---
16 files changed, 99 insertions(+), 110 deletions(-)
New commits:
commit 9984c41fb98d15becf3c29432c19cd7a266dece4
Author: Tom Lane <tgl(a)redhat.com>
Date: Thu Apr 30 19:09:07 2009 -0400
Fix some bogosities in newly added triggers (mostly NEW/OLD
confusion or accessing OLD where it's not guaranteed to exist).
diff --git a/schema/spacewalk/postgres/triggers/rhnChannel.sql b/schema/spacewalk/postgres/triggers/rhnChannel.sql
index b46861b..1b72993 100644
--- a/schema/spacewalk/postgres/triggers/rhnChannel.sql
+++ b/schema/spacewalk/postgres/triggers/rhnChannel.sql
@@ -19,19 +19,19 @@
create or replace function rhn_channel_mod_trig_fun() returns trigger as
$$
begin
- new.modified := current_timestamp;
+ new.last_modified := current_timestamp;
-- this is a really bad way of saying "if all we''re
-- changing is the date"
if tg_op='UPDATE' then
- if (old.id != new.id) or
- (old.parent_channel != new.parent_channel) or
- (old.org_id != new.org_id) or
- (old.channel_arch_id != new.channel_arch_id) or
- (old.label != new.label) or
- (old.basedir != new.basedir) or
- (old.name != new.name) or
- (old.summary != new.summary) or
- (old.description != new.description) then
+ if (old.id is distinct from new.id) or
+ (old.parent_channel is distinct from new.parent_channel) or
+ (old.org_id is distinct from new.org_id) or
+ (old.channel_arch_id is distinct from new.channel_arch_id) or
+ (old.label is distinct from new.label) or
+ (old.basedir is distinct from new.basedir) or
+ (old.name is distinct from new.name) or
+ (old.summary is distinct from new.summary) or
+ (old.description is distinct from new.description) then
new.modified := current_timestamp;
end if;
end if;
@@ -52,27 +52,22 @@ execute procedure rhn_channel_mod_trig_fun();
create or replace function rhn_channel_del_trig_fun() returns trigger as
$$
declare
- snapshots cursor for
- select snapshot_id
- from rhnSnapshotChannel
- where channel_id = old.id;
snapshot_curs_id numeric;
begin
- open snapshots;
+ for snapshot_curs_id in
+ select snapshot_id
+ from rhnSnapshotChannel
+ where channel_id = old.id
loop
- fetch snapshots into snapshot_curs_id;
- EXIT WHEN not found;
update rhnSnapshot
set invalid = lookup_snapshot_invalid_reason('channel_removed')
where id = snapshot_curs_id;
delete from rhnSnapshotChannel
where snapshot_id = snapshot_curs_id
and channel_id = old.id;
-
-
end loop;
- return new;
+ return old;
end;
$$ language plpgsql;
@@ -88,14 +83,11 @@ execute procedure rhn_channel_del_trig_fun();
create or replace function rhn_channel_access_trig_fun() returns trigger as
$$
begin
- if old.channel_access = 'protected' and
- new.channel_access != 'protected'
+ if old.channel_access = 'protected' and
+ new.channel_access is distinct from 'protected'
then
delete from rhnChannelTrust where channel_id = old.id;
end if;
-
- return new;
-
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnChannelComps.sql b/schema/spacewalk/postgres/triggers/rhnChannelComps.sql
index c11f904..f84f201 100644
--- a/schema/spacewalk/postgres/triggers/rhnChannelComps.sql
+++ b/schema/spacewalk/postgres/triggers/rhnChannelComps.sql
@@ -5,9 +5,17 @@ $$
begin
new.modified := current_timestamp;
- if new.last_modified = old.last_modified then
+ if tg_op='UPDATE' then
+ if new.last_modified = old.last_modified or
+ new.last_modified is null then
new.last_modified := current_timestamp;
+ end if;
+ else
+ if new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
end if;
+
return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql b/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql
index b0138ef..61ad2fe 100644
--- a/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql
+++ b/schema/spacewalk/postgres/triggers/rhnConfigChannel.sql
@@ -35,18 +35,13 @@ execute procedure rhn_confchan_mod_trig_fun();
create or replace function rhn_confchan_del_trig_fun() returns trigger as
$$
declare
- snapshots cursor is
- select snapshot_id as id
- from rhnSnapshotConfigChannel
- where config_channel_id = old.id;
-
snapshot_curs_id numeric;
begin
- open snapshots;
+ for snapshot_curs_id in
+ select snapshot_id as id
+ from rhnSnapshotConfigChannel
+ where config_channel_id = old.id
loop
- fetch snapshots into snapshot_curs_id;
- exit when not found;
-
update rhnSnapshot
set invalid = lookup_snapshot_invalid_reason('cc_removed')
where id = snapshot_curs_id;
@@ -56,7 +51,7 @@ begin
end loop;
- return new;
+ return old;
end;
$$ language plpgsql;
@@ -66,5 +61,5 @@ create trigger
rhn_confchan_del_trig
before delete on rhnConfigChannel
for each row
-execute procedure rhn_confchan_mod_trig_fun();
+execute procedure rhn_confchan_del_trig_fun();
diff --git a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
index 4c3795d..63db529 100644
--- a/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
+++ b/schema/spacewalk/postgres/triggers/rhnConfigRevision.sql
@@ -79,20 +79,13 @@ create or replace function rhn_confrevision_del_trig_fun() returns trigger
as
$$
declare
- snapshots cursor for
- select snapshot_id
- from rhnSnapshotConfigRevision
- where config_revision_id = old.id;
-
snapshot_curs_id numeric;
begin
-
- open snapshots;
-
+ for snapshot_curs_id in
+ select snapshot_id
+ from rhnSnapshotConfigRevision
+ where config_revision_id = old.id
loop
- fetch snapshots into snapshot_curs_id;
- exit when not found;
-
update rhnSnapshot
set invalid = lookup_snapshot_invalid_reason('cr_removed')
where id = snapshot_curs_id;
@@ -102,7 +95,7 @@ begin
end loop;
- return new;
+ return old;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql b/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql
index fcf1dbd..e86fc46 100644
--- a/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql
+++ b/schema/spacewalk/postgres/triggers/rhnErrataPackage.sql
@@ -23,14 +23,14 @@ $$
begin
if tg_op='INSERT' or tg_op='UPDATE' then
new.modified := current_timestamp;
+ return new;
end if;
if tg_op='DELETE' then
update rhnErrata
set rhnErrata.last_modified = current_timestamp
where rhnErrata.id in ( old.errata_id );
+ return old;
end if;
-
- return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql b/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql
index 1115fad..0c2ef8e 100644
--- a/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql
+++ b/schema/spacewalk/postgres/triggers/rhnKSTreeFile.sql
@@ -1,10 +1,19 @@
create or replace function rhn_kstreefile_mod_trig_fun() returns trigger as
$$
begin
- new.modified := current_timestamp;
- if new.last_modified = old.last_modified then
- new.last_modified := current_timestamp;
- end if;
+ new.modified := current_timestamp;
+
+ if tg_op='UPDATE' then
+ if new.last_modified = old.last_modified or
+ new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ else
+ if new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ end if;
+
return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql b/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql
index 238e025..9dfd453 100644
--- a/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql
+++ b/schema/spacewalk/postgres/triggers/rhnKickstartSession.sql
@@ -38,7 +38,7 @@ create or replace function rhn_ks_session_history_trigger_fun() returns trigger
as
$$
begin
- if tg_op ='INSERT' or (tg_op='UPDATE' and new.state_id != old.state_id) then
+ if tg_op ='INSERT' then
insert into rhnKickstartSessionHistory (
id, kickstart_session_id, action_id, state_id
) values (
@@ -48,10 +48,18 @@ begin
new.state_id
);
end if;
-
- return new;
-
-
+ if tg_op ='UPDATE' then
+ if new.state_id is distinct from old.state_id then
+ insert into rhnKickstartSessionHistory (
+ id, kickstart_session_id, action_id, state_id
+ ) values (
+ nextval('rhn_ks_sessionhist_id_seq'),
+ new.id,
+ new.action_id,
+ new.state_id
+ );
+ end if;
+ end if;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql
index fe542b3..600b022 100644
--- a/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql
+++ b/schema/spacewalk/postgres/triggers/rhnKickstartableTree.sql
@@ -1,13 +1,19 @@
create or replace function rhn_kstree_mod_trig_fun() returns trigger as
$$
begin
- if (new.last_modified = old.last_modified) or
- (new.last_modified is null ) then
- new.last_modified := sysdate;
- end if;
-
new.modified := current_timestamp;
-
+
+ if tg_op='UPDATE' then
+ if new.last_modified = old.last_modified or
+ new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ else
+ if new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ end if;
+
return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnPackage.sql b/schema/spacewalk/postgres/triggers/rhnPackage.sql
index b2bcefa..3e445e6 100644
--- a/schema/spacewalk/postgres/triggers/rhnPackage.sql
+++ b/schema/spacewalk/postgres/triggers/rhnPackage.sql
@@ -1,14 +1,20 @@
create or replace function rhn_package_mod_trig_fun() returns trigger as
$$
begin
-
- if new.last_modified = old.last_modified then
- new.last_modified :=current_timestamp;
- end if;
+ new.modified := current_timestamp;
- new.modified := current_timestamp;
-
- return new;
+ if tg_op='UPDATE' then
+ if new.last_modified = old.last_modified or
+ new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ else
+ if new.last_modified is null then
+ new.last_modified := current_timestamp;
+ end if;
+ end if;
+
+ return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnServerAction.sql b/schema/spacewalk/postgres/triggers/rhnServerAction.sql
index c501433..5bceaee 100644
--- a/schema/spacewalk/postgres/triggers/rhnServerAction.sql
+++ b/schema/spacewalk/postgres/triggers/rhnServerAction.sql
@@ -6,7 +6,7 @@ begin
new.modified := current_timestamp;
handle_status := 0;
if TG_OP = 'UPDATE' then
- if new.status != old.status then
+ if new.status is distinct from old.status then
handle_status := 1;
end if;
else
diff --git a/schema/spacewalk/postgres/triggers/rhnServerGroup.sql b/schema/spacewalk/postgres/triggers/rhnServerGroup.sql
index 338a602..6bd7e88 100644
--- a/schema/spacewalk/postgres/triggers/rhnServerGroup.sql
+++ b/schema/spacewalk/postgres/triggers/rhnServerGroup.sql
@@ -73,19 +73,13 @@ create or replace function rhn_sg_del_trig_fun() returns trigger
as
$$
declare
- snapshots cursor for
+ snapshot_curs_id numeric;
+begin
+ for snapshot_curs_id in
select snapshot_id
from rhnSnapshotServerGroup
- where server_group_id = old.id;
-
- snapshot_curs_id numeric;
-begin
-
- open snapshots;
+ where server_group_id = old.id
loop
- fetch snapshots into snapshot_curs_id;
- exit when not found;
-
update rhnSnapshot
set invalid = lookup_snapshot_invalid_reason('sg_removed')
where id = snapshot_curs_id;
@@ -95,18 +89,13 @@ begin
end loop;
- return new;
-
+ return old;
end;
$$
language plpgsql;
-
-
create trigger
rhn_sg_del_trig
before delete on rhnServerGroup
for each row
execute procedure rhn_sg_del_trig_fun();
-
-
diff --git a/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql b/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql
index 72af9d3..2e9b077 100644
--- a/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql
+++ b/schema/spacewalk/postgres/triggers/rhnServerGroupMembers_email.sql
@@ -47,7 +47,6 @@ create or replace function rhn_sg_member_email_del_trig_fun() returns trigger as
$$
begin
perform rhn_email.add_for_server(old.server_id);
- return new;
end;
$$
language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql
index 9262b72..cc68dd8 100644
--- a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql
+++ b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers.sql
@@ -96,7 +96,7 @@ begin
set current_members = current_members - 1
where id = old.user_group_id;
- return new;
+ return old;
end;
$$
language plpgsql;
@@ -132,8 +132,6 @@ begin
UPDATE web_contact SET password = old_password WHERE id = old.user_id;
END IF;
END IF;
-
- RETURN NEW;
end;
$$ LANGUAGE PLPGSQL;
diff --git a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql
index 7ce7940..4989b77 100644
--- a/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql
+++ b/schema/spacewalk/postgres/triggers/rhnUserGroupMembers_email.sql
@@ -45,8 +45,6 @@ as
$$
begin
perform rhn_email.add_for_user(old.user_id);
-
- return new;
end;
$$ language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql b/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql
index f2c6829..b15633b 100644
--- a/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql
+++ b/schema/spacewalk/postgres/triggers/rhnUserServerGroupPerms.sql
@@ -43,7 +43,7 @@ $$
begin
perform rhn_email.add_for_user(old.user_id);
- return new;
+ return old;
end;
$$
language plpgsql;
diff --git a/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql b/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql
index 68867ef..0c81224 100644
--- a/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql
+++ b/schema/spacewalk/postgres/triggers/rhn_contact_methods.sql
@@ -1,12 +1,6 @@
create or replace function rhn_cmeth_val_trig_fun() returns trigger as
$$
-declare
- msg varchar(200);
- --missing_data exception;
begin
-
- msg :='missing or invalid data for contact_methods table';
-
if new.method_type_id = 1 then
--- pager fields pager_email,pager_split_long_messages should be not null
@@ -31,13 +25,7 @@ begin
end if;
end if;
-
-
- if not found then
- perform rhn_exception.raise_exception(msg);
- end if;
-
- return new;
+ return new;
end;
$$ language plpgsql;
