Solved

Translate Following PL-SQL trigger to T-SQL trigger

Posted on 2004-09-01
8
580 Views
Last Modified: 2007-12-19
Hi everybody. I got a question as stated in title. Is there anybody know how to translate follwing  PL-SQL trigger to T-SQL trigger?

CREATE OR REPLACE TRIGGER trigger1
BEFORE UPDATE ON employee

FOR EACH ROW
WHEN (NEW.sal = 100)
BEGIN
  -- some actions here --
END;

Whenever any record in employee table is going to be updated to 100, some actions will be taken before that update (eg. change the new value from 100 to 120, then update the relevant records with 120 rather 100).

Thanks a lot.
?@_@?
0
Comment
Question by:lenzge
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 4

Assisted Solution

by:chipple
chipple earned 40 total points
ID: 11960359
This should work:

CREATE TRIGGER trigger1 ON employee
FOR UPDATE
AS
  DECLARE @sal int
  SELECT @sal=sal FROM inserted

  IF @sal = 100
  BEGIN
    -- some actions here --
  END

Good luck!
0
 
LVL 11

Expert Comment

by:ram2098
ID: 11960392


CREATE TRIGGER trigger1 ON employee
FOR UPDATE
AS
  DECLARE @sal int

  SET ROWCOUNT 1
  WHILE EXISTS (SELECT sal FROM inserted WHERE SAL = 100)
      BEGIN
          --YOUR STATEMENTS HERE
          DELETE FROM INSERTED WHERE SAL = 100
      END
  SET ROWCOUNT 0
0
 
LVL 4

Expert Comment

by:chipple
ID: 11960410
ram2098 is on the money.
I guess I'm a bit rusty. :)
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 26

Expert Comment

by:Hilaire
ID: 11960931
inserted and deleted are read-only
you can't delete from "deleted"

you'll have to use a cursor

CREATE TRIGGER trigger1
FOR UPDATE ON employee
as
declare c1 cursor for select empid from employee where sal=100
declare @empid int
open c1
fetch c1 into @empid
while @@fetch_status = 0
begin
 -- do whatever you want here for this @empid
 fetch c1 into @empid
end
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11960934
Oops Cursr should be on inserted virtuak table, not employee

ALTER TRIGGER trigger1
FOR UPDATE ON employee
as
declare c1 cursor for select empid from inserted where sal=100
declare @empid int
open c1
fetch c1 into @empid
while @@fetch_status = 0
begin
 -- do whatever you want here for this @empid
 fetch c1 into @empid
end
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 11961846
IMHO,   I think you should look at the  -- some actions here --
and ask yourself "Does this really need to be in a cursor?"

cursors are best avoided unless you need them, expecially in triggers.
0
 

Author Comment

by:lenzge
ID: 11983388
What I'd done is as follow:

CREATE TRIGGER Trigger1
FOR UPDATE ON employee
AS

DECLARE @emp_id CHAR(4)
DECLARE @sal INT
SELECT @emp_id = emp_id FROM INSERTED
SELECT @sal = sal FROM INSERTED

IF @sal = 100
  UPDATE employee (sal) VALUES (120)

Basically, what I need to do is change a particular field value of newly inserted record if a criteria is fulfilled. Any comment to improve it?

? @_@ ?
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 30 total points
ID: 11988478
Basicallly this trigger will fail and /or cause big problems when someone performs an update on more than one record.

This is better:

CREATE TRIGGER Trigger1
FOR UPDATE ON employee
AS
UPDATE e
 SET sal=120
 FROM employee e
  INNER JOIN INSERTED i ON e.emp_id=i.emp_id
 WHERE i.sal=100
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question