help! Oracle trigger conversion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help! Oracle trigger conversion

Author

Topic

Dargon
Starting Member

26 Posts

Posted - 2005-06-24 : 18:21:10

Hi,
I am new in SQL, I need help to convert trigger written on Oracle.
It looks that upon entering AuthorizedBy
it sets AuthorizedDate to sysdate if no date provided, otherwise uses provided date,
otherwise sets AuthorizedDate to null.
Any help would be appreciated,

Here it is:

CREATE OR REPLACE TRIGGER trg_optproj_authorizedby
BEFORE INSERT OR UPDATE
OF authorizedby
ON OPTIMAINPROJECTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN

IF :NEW.authorizedby IS NOT NULL THEN

IF :NEW.authorizeddate IS NULL THEN
:NEW.authorizeddate := SYSDATE;
ELSE
:NEW.authorizeddate := :NEW.authorizeddate;
END IF;

IF :OLD.status <> 'COMPLETED' THEN
:NEW.status := 'AUTHORIZED';
END IF;

ELSE

:NEW.authorizeddate := NULL;

IF :OLD.status = 'AUTHORIZED' THEN
:NEW.status := 'LOCKED';
END IF;

END IF;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END;

Dargon.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-06-24 : 20:11:46

Where Oracle uses the New and Old virtual tables within a trigger, Microsoft uses the Inserted and Deleted virtual tables.

The actual trigger definition systax is slightly different but you can look that up in BOL. Also, MS doesn't loop through the rows. You perform the actual work and associated logic in the query.

This is completely untested but should get you headed in the right direction.

[Code]
CREATE TRIGGER trg_optproj_authorizedby
ON OPTIMAINPROJECTS
for INSERT, UPDATE

BEGIN
update OPTIMAINPROJECTS
set authorizeddate =
case when i.authorizedby is Null
then Null
when i.authorizedby is not Null
and i.authorizeddate IS NULL
then Current_Timestamp() -- GetDate() if you prefer
else authorizeddate
end,
status =
case when i.authorizedby is Null
and d.status = 'AUTHORIZED'
then 'LOCKED'
when i.authorizedby is not Null
and d.Status <> 'COMPLETED'
then 'AUTHORIZED'
else status
end
from OPTIMAINPROJECTS omp
join Inserted i
on i.PrimaryKey = omp.PrimaryKey
join deleted d
on i.PrimaryKey = omp.PrimaryKey

if @@Error <> 0 then
raiserror '<Your error message>', <Error number reflecting the severity>
END

[/Code]

HTH

=================================================================
'Tis with our judgements as our watches: none Go just alike, yet each believes his own. -Alexander Pope, poet (1688-1744)

sanjnep
Posting Yak Master

191 Posts

Posted - 2005-06-24 : 20:16:50

Please visit this site.You will get your answer.
http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm
Thanks
Sanjeev Shrestha
10 Lancewood way
Irvine ,California 92612

Sanjeev shrestha

Dargon
Starting Member

26 Posts

Posted - 2005-06-27 : 11:44:19

Thanks, Bustaz Kool, It works!
Actually I had to modify syntax a little bit:
create TRIGGER trg_optproj_authorizedby
ON OPTIMAINPROJECTS
for INSERT, UPDATE
as
BEGIN

declare @INauthorizedby varchar(100)
declare @INauthorizeddate varchar(100)
declare @INstatus varchar(100)
set @INauthorizedby=(Select i.authorizedby from inserted i, OPTIMAINPROJECTS op where op.ProjectID=i.ProjectID)
set @INauthorizeddate=(Select i.authorizedby from inserted i, OPTIMAINPROJECTS op where op.ProjectID=i.ProjectID)
set @INstatus=(Select d.status from deleted d, OPTIMAINPROJECTS op where op.ProjectID=d.ProjectID)
update OPTIMAINPROJECTS
set authorizeddate =
case when @INauthorizedby is Null
then Null
when @INauthorizedby is not Null
and @INauthorizeddate IS NULL
then GetDate()
else authorizeddate
end
,
status =
case when @INauthorizedby is Null
and @INstatus = 'AUTHORIZED'
then 'LOCKED'
when @INauthorizedby is not Null
and @INstatus <> 'COMPLETED'
then 'AUTHORIZED'
else status
end

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources