Oracle trigger to MS SQL Server 2008 trigger

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

General SQL Server Forums

New to SQL Server Programming

Oracle trigger to MS SQL Server 2008 trigger

Author

Topic

kileriokas
Starting Member

3 Posts

Posted - 2011-05-19 : 16:55:30

hello,

I'm new in ms sql server 2008. I am doing my bachalor finish work and I want to convert oracle trigger code to ms sql server code, because I need to do comparason between these two database systems. Is it possible to convert these triggers to ms sql triggers? Can anyone help me? Here are oracle triggers:

create or replace trigger buyer_seller_tel_nr
before insert or update on buyer_seller
for each row
declare
-- local variables here
begin
if substr(:new.telefon, 1, 2)='86' then
:new.telefon:='+370'||substr(:new.telefon, 2, length(:new.telefon));
end if;
end buyer_seller_tel_nr;

and another:
create or replace trigger saskaita_faktura_aud
before insert or update or delete on saskaita_faktura
for each row
declare
-- local variables here
begin
if inserting
then
insert into saskaita_faktura_aud
values
('INSERT',
USER,
SYSDATE,
:new.SASK_FAKT_ID,
:new.PIRKEJO_ID,
:new.PARDAVEJO_ID,
:new.SERIJA,
:new.NUMERIS,
:new.PR_PASLAUG_ID,
:new.MATO_ID,
:new.PARDAVIMO_DATA,
:new.KIEKIS,
:new.KAINA_BE_PVM,
:new.NUOLAIDA_ID,
:new.PVM_ID,
:new.SUMA_BE_PVM,
:new.PVM_SUMA,
:new.SUMA_SU_NUOLAIDA,
:new.SUMA_SU_PVM,
:new.IS_VISO_BE_PVM,
:new.IS_VISO_SU_PVM,
:new.APMOKEJIMO_ID,
:new.SASK_ISRASYMO_DATA,
:new.SASKAITA_APMOKETI_IKI,
:new.SUMA_ZODZIAIS,
:new.SASKAITA_ISRASE,
:new.PREKES_ISDAVE,
:new.PREKES_PRIEME);
elsif updating
then
insert into saskaita_faktura_aud
values
('UPDATE',
USER,
SYSDATE,
:new.SASK_FAKT_ID,
:new.PIRKEJO_ID,
:new.PARDAVEJO_ID,
:new.SERIJA,
:new.NUMERIS,
:new.PR_PASLAUG_ID,
:new.MATO_ID,
:new.PARDAVIMO_DATA,
:new.KIEKIS,
:new.KAINA_BE_PVM,
:new.NUOLAIDA_ID,
:new.PVM_ID,
:new.SUMA_BE_PVM,
:new.PVM_SUMA,
:new.SUMA_SU_NUOLAIDA,
:new.SUMA_SU_PVM,
:new.IS_VISO_BE_PVM,
:new.IS_VISO_SU_PVM,
:new.APMOKEJIMO_ID,
:new.SASK_ISRASYMO_DATA,
:new.SASKAITA_APMOKETI_IKI,
:new.SUMA_ZODZIAIS,
:new.SASKAITA_ISRASE,
:new.PREKES_ISDAVE,
:new.PREKES_PRIEME);
ELSE
insert into saskaita_faktura_aud
values
('DELETE',
USER,
SYSDATE,
:old.SASK_FAKT_ID,
:old.PIRKEJO_ID,
:old.PARDAVEJO_ID,
:old.SERIJA,
:old.NUMERIS,
:old.PR_PASLAUG_ID,
:old.MATO_ID,
:old.PARDAVIMO_DATA,
:old.KIEKIS,
:old.KAINA_BE_PVM,
:old.NUOLAIDA_ID,
:old.PVM_ID,
:old.SUMA_BE_PVM,
:old.PVM_SUMA,
:old.SUMA_SU_NUOLAIDA,
:old.SUMA_SU_PVM,
:old.IS_VISO_BE_PVM,
:old.IS_VISO_SU_PVM,
:old.APMOKEJIMO_ID,
:old.SASK_ISRASYMO_DATA,
:old.SASKAITA_APMOKETI_IKI,
:old.SUMA_ZODZIAIS,
:old.SASKAITA_ISRASE,
:old.PREKES_ISDAVE,
:old.PREKES_PRIEME);
end if;
end saskaita_faktura_aud;

create table SASKAITA_FAKTURA_AUD
(
OPERATION VARCHAR2(10),
P_USER VARCHAR2(50),
P_DATE DATE,
SASK_FAKT_ID NUMBER(10),
PIRKEJO_ID NUMBER(10),
PARDAVEJO_ID NUMBER(10),
SERIJA VARCHAR2(3),
NUMERIS NUMBER(10),
PR_PASLAUG_ID NUMBER(10),
MATO_ID NUMBER(10),
PARDAVIMO_DATA VARCHAR2(15),
KIEKIS NUMBER(10),
KAINA_BE_PVM NUMBER(10,2),
NUOLAIDA_ID NUMBER(4),
PVM_ID NUMBER(3),
SUMA_BE_PVM NUMBER(10,2),
PVM_SUMA NUMBER(10,2),
SUMA_SU_NUOLAIDA NUMBER(10,2),
SUMA_SU_PVM NUMBER(10,2),
IS_VISO_BE_PVM NUMBER(10,2),
IS_VISO_SU_PVM NUMBER(10,2),
APMOKEJIMO_ID NUMBER(3),
SASK_ISRASYMO_DATA VARCHAR2(15),
SASKAITA_APMOKETI_IKI VARCHAR2(15),
SUMA_ZODZIAIS VARCHAR2(100),
SASKAITA_ISRASE VARCHAR2(60),
PREKES_ISDAVE VARCHAR2(60),
PREKES_PRIEME VARCHAR2(60)
)

Thank you for helping me

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-19 : 17:22:09

Normally we don't answer exam/homework/certification questions but the second trigger is pretty easy:

CREATE TRIGGER trg_saskaita_faktura_aud ON saskaita_faktura 
FOR INSERT, UPDATE, DELETE AS
SET NOCOUNT ON 
IF EXISTS(SELECT * FROM inserted) -- update or insert
BEGIN
	INSERT INTO saskaita_faktura_aud
	SELECT CASE WHEN EXISTS(SELECT * FROM deleted) THEN 'UPDATE' ELSE 'INSERT' END,
	USER_NAME(),
	GETDATE(),
	SASK_FAKT_ID,
	PIRKEJO_ID,
	PARDAVEJO_ID,
	SERIJA,
	NUMERIS,
	PR_PASLAUG_ID,
	MATO_ID,
	PARDAVIMO_DATA,
	KIEKIS,
	KAINA_BE_PVM,
	NUOLAIDA_ID,
	PVM_ID,
	SUMA_BE_PVM,
	PVM_SUMA,
	SUMA_SU_NUOLAIDA,
	SUMA_SU_PVM,
	IS_VISO_BE_PVM,
	IS_VISO_SU_PVM,
	APMOKEJIMO_ID,
	SASK_ISRASYMO_DATA,
	SASKAITA_APMOKETI_IKI,
	SUMA_ZODZIAIS,
	SASKAITA_ISRASE,
	PREKES_ISDAVE,
	PREKES_PRIEME
	FROM inserted;
END
ELSE
BEGIN
	INSERT INTO saskaita_faktura_aud
	SELECT 'DELETE',
	USER_NAME(),
	GETDATE(),
	SASK_FAKT_ID,
	PIRKEJO_ID,
	PARDAVEJO_ID,
	SERIJA,
	NUMERIS,
	PR_PASLAUG_ID,
	MATO_ID,
	PARDAVIMO_DATA,
	KIEKIS,
	KAINA_BE_PVM,
	NUOLAIDA_ID,
	PVM_ID,
	SUMA_BE_PVM,
	PVM_SUMA,
	SUMA_SU_NUOLAIDA,
	SUMA_SU_PVM,
	IS_VISO_BE_PVM,
	IS_VISO_SU_PVM,
	APMOKEJIMO_ID,
	SASK_ISRASYMO_DATA,
	SASKAITA_APMOKETI_IKI,
	SUMA_ZODZIAIS,
	SASKAITA_ISRASE,
	PREKES_ISDAVE,
	PREKES_PRIEME
	FROM deleted;
END
GO

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-19 : 17:23:22

can you post what you have tried so far?

and can you post the DDL for the Tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

kileriokas
Starting Member

3 Posts

Posted - 2011-05-20 : 16:53:09

Thank you a lot, I tried to convert my first trigger:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER dbo.trigger1(trigerio pavadinimas) on dbo.project (lentele)
AFTER INSERT, UPDATE
AS
BEGIN
declare @newNumber varchar(20)
select @newNumber = telefonas from inserted

if(left(@newNumer,2) = '86')
begin
set @newNumber = '+370'+substring(@newNumber,3,len(@newNumber)-2)
update pirkejas_pardavejas set telefonas = @newNUmber where pirk_pard_id = inserted.pirk_pard_id
end
END
GO

And I get a message: Msg 4104, Level 16, State 1, Procedure trigger1, Line 12
The multi-part identifier "inserted.pirk_pard_id" could not be bound. what's wrong with this trigger?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-20 : 17:06:09

inserted is a table (more accurately, a pseudo-table), and SQL Server triggers do not have implicit cursors on the rows like Oracle triggers do. Instead you JOIN to inserted (or deleted) and process like a regular UPDATE statement:

CREATE TRIGGER dbo.trigger1 on dbo.project
AFTER INSERT, UPDATE
AS 
	update a set telefonas = '+370'+substring(telefonas,3,len(telefonas)-2)
	from pirkejas_pardavejas a
	inner join inserted i on a.pirk_pard_id = i.pirk_pard_id
	where i.telefonas LIKE '86%'
GO

kileriokas
Starting Member

3 Posts

Posted - 2011-05-20 : 17:08:33

Thank you :) It was helpful!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources