Converting PostgreSQL triggers to SQL Server 2008

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 2008 Forums

Other SQL Server 2008 Topics

Converting PostgreSQL triggers to SQL Server 2008

Author

Topic

akit
Starting Member

2 Posts

Posted - 2009-10-13 : 20:49:11

Hi all

I'm currently working on transferring a database to SQL Server from Postgres and have been having trouble with SQL Server triggers. I have a trigger in Postgres which is fairly straight forward but the process seems a bit more complicated in SQL Server.
Basically I'm automatically filling a field value based on user input in different field. I initiate the trigger in Postgres before the record has been written to the database (which is an 'INSTEAD OF' process in SQL Server I think). Anyway I haven't had much luck getting a trigger working in SQL Server. Below is the trigger script from Postgres. There are two parts, the trigger and trigger function.

Does anyone out there know how to create the same function in SQL Server via triggers or any other way?

CREATE TRIGGER taggroup_val
BEFORE INSERT OR UPDATE
ON tag
FOR EACH ROW
EXECUTE PROCEDURE set_taggroup();

========================================================

CREATE OR REPLACE FUNCTION set_taggroup()
RETURNS trigger AS
$BODY$
BEGIN

IF NEW.idtagtype <= 2 THEN
NEW.taggroup = 'Dart_Tbar';
ELSE
NEW.taggroup = 'PIT';
END IF;

RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION set_taggroup() OWNER TO postgres;

Cheers

Adrian

akit
Starting Member

2 Posts

Posted - 2009-10-18 : 22:49:27

I seem to have replaced the above trigger in SQL Server using the following code.

CREATE TRIGGER [dbo].[set_TagGroup]
ON [dbo].[tag]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

IF UPDATE(idtagtype)
UPDATE dbo.tag SET taggroup = 'Dart_Tbar' WHERE idtagtype <= 2;
UPDATE dbo.tag SET taggroup = 'PIT' WHERE idtagtype > 2;

END

Although the trigger works it looks quite clunky and I'm sure there is a more optimal form. I haven't been able to find any 'IF THEN' examples to filter field values within a trigger. Any suggestions?

Adrian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources