Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 615
  • Last Modified:

Translate Following PL-SQL trigger to T-SQL trigger

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
lenzge
Asked:
lenzge
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
chippleCommented:
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
 
ram2098Commented:


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
 
chippleCommented:
ram2098 is on the money.
I guess I'm a bit rusty. :)
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
HilaireCommented:
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
 
HilaireCommented:
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
 
ShogunWadeCommented:
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
 
lenzgeAuthor Commented:
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
 
ShogunWadeCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now