|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
June 16th, 2004, 11:21 AM
|
|||
|
|||
MS Sql Trigger help needed
Hello,
I created following trigger in oracle and wnat to convert for ms-sql database. Please help converting the following trigger to ms-sql CREATE OR REPLACE TRIGGER negtwo_record AFTER UPDATE ON twdbs.transactions REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW declare t_negtwo_date date; begin --as each update is applied, if the pmflag is set to -2, record a copy of that record out to the table_negtwo_tracking table. select sysdate into t_negtwo_date from dual; if :new.PMFLAG = '-2' then begin insert into table_negtwo_tracking (TRANS_ID, EMP_ID, TRANS_DATE, ACTIVITY_ID, RESOURCE_ID, TRANS_TYPE, PMFLAG, NEGTWO_DATE) values :new.TRANS_ID, :new.EMP_ID, :new.TRANS_DATE, :new.ACTIVITY_ID, :new.RESOURCE_ID, :new.TRANS_TYPE, :new.PMFLAG, t_negtwo_date); end; else null; end if; end; Best Regards, Prashant Dalal
|
|
#2
June 17th, 2004, 04:35 AM
|
|||
|
|||
|
This should do it:
Code:
CREATE TRIGGER negtwo_record ON twdbs.[Table Owner].transactions -- Actually you only need the name of the [Table Owner].[Table Name] unless the table is located in another database AFTER UPDATE AS DECLARE @t_negtwo_date datetime SET @t_negtwo_date = GETDATE() INSERT INTO table_negtwo_tracking (TRANS_ID, EMP_ID, TRANS_DATE, ACTIVITY_ID, RESOURCE_ID, TRANS_TYPE, PMFLAG, NEGTWO_DATE) SELECT new.TRANS_ID, new.EMP_ID, new.TRANS_DATE, new.ACTIVITY_ID, new.RESOURCE_ID, new.TRANS_TYPE, new.PMFLAG, @t_negtwo_date FROM inserted as new WHERE new.PMFLAG = '-2' ----------------------------- Pascal Dobrautz http://www.sqlassi.net
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > MS Sql Trigger help needed |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
