Neede trigger - SQL Server Forums

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

Transact-SQL (2005)

Neede trigger

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-29 : 09:54:27

[code]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TR_INSERT_EMRTransactions]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TR_INSERT_EMRTransactions]
GO

CREATE TRIGGER [dbo].[TR_INSERT_EMRTransactions] ON EMRTRANSACTIONS FOR INSERT
AS
BEGIN
case ROLE_NAME
when 'CONSULTANT' then
INSERT INTO EMRCOTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
when 'NURSE PRACTITIONER' then
INSERT INTO EMRNPTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
when 'NURSE' then
INSERT INTO EMRNUTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
when 'PHYSICIAN ASSISTANT' then
INSERT INTO EMRPATRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
when 'ACCOUNTS' then
INSERT INTO EMRACTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
else
INSERT INTO EMRALLTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
END CASE
GO

throwing exception as

Msg 156, Level 15, State 1, Procedure TR_DELETE_EMRTransactions, Line 5
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Procedure TR_DELETE_EMRTransactions, Line 8
Incorrect syntax near the keyword 'when'.

please help me out

[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 10:00:04

You cannot use CASE without SELECT or so because it is not a stand alone statement.
Where is ROLE_NAME coming from? If it is a var then it should be @ROLE_NAME and should be declared...

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-29 : 10:03:52

HI i.e column_name and is a filed available in that table can you please help in giving the above trigger please

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-29 : 10:10:59

I fail to see the need and logic in this trigger
May be it would be better to insert data using a procedure?

it seems that when inserting into one table you want to insert into different tables based on the field value, so the table works like a buffer where you pass data
I would go for stored procedure if that is what you need.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-29 : 10:12:56

HI WRITTEN LIKE THIS

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TR_INSERT_EMRTransactions]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TR_INSERT_EMRTransactions]
GO

CREATE TRIGGER [dbo].[TR_INSERT_EMRTransactions] ON
EMRTRANSACTIONS FOR INSERT
AS
BEGIN
DECLARE @ROLE_NAME VARCHAR(20);
SELECT @ROLE_NAME = (SELECT ROLE_NAME FROM inserted)
SELECT
case @ROLE_NAME
when 'CONSULTANT' then
'INSERT INTO EMRCOTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
when 'NURSE PRACTITIONER' then
'INSERT INTO EMRNPTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
when 'NURSE' then
'INSERT INTO EMRNUTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
when 'PHYSICIAN ASSISTANT' then
'INSERT INTO EMRPATRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
when 'ACCOUNTS' then
'INSERT INTO EMRACTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
else
'INSERT INTO EMRALLTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED'
END
END
GO

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-29 : 10:19:09

please stop using sql

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-29 : 10:23:34

can you give me that stored procedure as it woold be very helpful tome please.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-09-29 : 10:44:50

[code]CREATE TRIGGER [dbo].[TR_INSERT_EMRTransactions] ON EMRTRANSACTIONS FOR INSERT AS
SET NOCOUNT ON
INSERT INTO EMRCOTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED WHERE ROLE_NAME='CONSULTANT'

INSERT INTO EMRNPTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED WHERE ROLE_NAME='NURSE PRACTITIONER'

INSERT INTO EMRNUTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED WHERE ROLE_NAME='NURSE'

INSERT INTO EMRPATRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED WHERE ROLE_NAME='PHYSICIAN ASSISTANT'

INSERT INTO EMRACTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED WHERE ROLE_NAME='ACCOUNTS'

INSERT INTO EMRALLTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED
WHERE ROLE_NAME NOT IN('CONSULTANT','NURSE PRACTITIONER','NURSE','PHYSICIAN ASSISTANT','ACCOUNTS')[/code]Some comments:

-In SQL, CASE is an expression, not a statement. It must be written to return a value, like a function.
-You would do well to read up on SQL Server before you continue developing for it. For no other reason than:
-Multiple tables with the same exact structure is a bad practice. Especially since you're inserting the same data as in your original table. None of the tables you're inserting into with this trigger need to exist. You can create views of the same name as the table if you need to present data that way:[code]CREATE VIEW EMRCOTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS WHERE ROLE_NAME='CONSULTANT'
GO

CREATE VIEW EMRNPTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS WHERE ROLE_NAME='NURSE PRACTITIONER'
GO

CREATE VIEW EMRNUTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS WHERE ROLE_NAME='NURSE'
GO

CREATE VIEW EMRPATRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS WHERE ROLE_NAME='PHYSICIAN ASSISTANT'
GO

CREATE VIEW EMRACTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS WHERE ROLE_NAME='ACCOUNTS'
GO

CREATE VIEW EMRALLTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE) AS
SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM EMRTRANSACTIONS
WHERE ROLE_NAME NOT IN('CONSULTANT','NURSE PRACTITIONER','NURSE','PHYSICIAN ASSISTANT','ACCOUNTS')
GO[/code]Using these views, you'll have only 1 table with data, therefore nothing to synchronize, and therefore no need for a trigger to do it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 10:45:58

Your trigger needs no CASE ...

Just do this in your trigger
insert into EMRCOTRANSACTIONS (EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE)
select SELECT EMR_TRANSACTION_ID, FLOW_ID, NODE_DEFINITION_ID, ROLE_NAME, USER_LOGIN, PATIENT_ID, TASK_ID, ID_VALUE FROM INSERTED where ROLE_NAME = 'CONSULTANT'

and so on ...

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-29 : 10:46:45

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-09-30 : 02:12:56

IS there any other way to use with case

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-30 : 02:30:59

Uuh! What a question...

No, you're never too old to Yak'n'Roll if you're too young to die.

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-30 : 06:07:36

Well webfred, you should answer it :)

Actually there is, but its a difficult one and dangerous one only the elite masters of sql can do it. Like Peso and the other guys with response count over 10K. But they will tell you that its impossible.

I imagine that you can cycle through our inserted table use CASE and dynamic sql and it works. But if you try it and your inner sql spirit is not strong enough, or you don't have enough posts in this forum, the ghost of sql server will come to you in your sleep and kill you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources