0

Also following error coming near "declare @empsal" as PLS-00103 :Encountered the symbol "@" when expecting one of the following...

My tables do exist in the database.

My trigger:

create or replace
TRIGGER leavemastertrg After INSERT 
ON "xyz"."leave_master"
    declare @empid int;
    declare @empname varchar(100);
    declare @empsal decimal(10,2);
    declare @audit_action varchar(100);

    select @empid=i.LEAVE_ID from inserted i;   
    select @empname=i.LEAVE_NAME from inserted i;   
    select @empsal=i.LEAVE_STATUS from inserted i;  
    set @audit_action='Inserted Record -- After Insert Trigger.';

    insert into manager_master
           (MANAGER_ID,MANAGER_NAME,MANAGER_STATUS,MANAGER_AUDIT_ACTION,MANAGER_AUDIT_TIMESTAMP) 
    values(@empid,@empname,@empsal,@audit_action,getdate());

    PRINT 'AFTER INSERT trigger fired.'
GO
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Saro
  • 1
  • 1
  • There is no "declare @empsal" in the code that you have posted. Please post the real code that you are using and put all the error messages that you get in your question (not in the title), including the line number at which they occur – Erwin Bolwidt Mar 06 '15 at 04:44
  • 2
    What database are you using? You've tagged this for `oracle` but the code you've posted doesn't appear to be PL/SQL. It appears to be SQL Server T-SQL. You've tagged this for `MySQL` but the error you've posted seems to be an Oracle error not a MySQL error. My guess is that you're trying to use SQL Server syntax in an Oracle database which is fundamentally not going to work. I'm hard-pressed to see where MySQL comes into play. – Justin Cave Mar 06 '15 at 04:48
  • Sorry am newbie to this trigger @Justin Cave.initially i practiced basics from [link](http://www.codeproject.com/Articles/25600/Triggers-SQL-Server) in MySql server only,there also given error some.Then tried in Oracle sql,with same code & some modification.@ErwinBolwidt above one was my exact code in oracle.(MySQL 5.6 using) – Saro Mar 06 '15 at 05:17
  • You linked to an article about developing triggers in SQL Server. Trying to run that code in either MySQL or Oracle is going to fail. Each database supports a very different procedural language for things like triggers or procedures (in addition to supporting different dialects of SQL). You can't generally take code written for one database and just run it in a different database. If you want to follow that tutorial, you'd need to use a SQL Server database. If you want to use Oracle or MySQL, you'd want to find a tutorial specific to that database. – Justin Cave Mar 06 '15 at 05:21
  • CREATE TRIGGER trgAfterInsert ON classicmodels.Employee_Test FOR INSERT AS declare empid int; declare empname varchar(100); declare empsal decimal(10,2); declare audit_action varchar(100); select @empid=i.Emp_ID from inserted i; select @empname=i.Emp_Name from inserted i; select @empsal=i.Emp_Sal from inserted i; set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Test_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@empid,@empname,@empsal,@audit_action,getdate()); PRINT 'AFTER INSERT trigger fired.' GO – Saro Mar 06 '15 at 05:27
  • following is MySQL error: 10:51:06 CREATE TRIGGER trgAfterInsert ON classicmodels.Employee_Test FOR INSERT AS declare @empid int Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON classicmodels.Employee_Test FOR INSERT AS declare empid int' at line 1 0.000 sec – Saro Mar 06 '15 at 05:29
  • Triggers are usually product specific. Only a very few dbms products are ANSI SQL compliant when it comes to triggers, so tag properly top get the best answers! – jarlh Mar 06 '15 at 07:36

1 Answers1

0

CREATE OR REPLACE TRIGGER LM_AFTER_INSERT AFTER INSERT ON LEAVE_MASTER FOR EACH ROW

DECLARE v_username varchar2(20 byte);

BEGIN SELECT user INTO v_username FROM dual;

INSERT INTO manager_master
( MANAGER_ID,
 MANAGER_NAME,
 MANAGER_STATUS,
 Modified_by)
VALUES
( :new.LEAVE_ID,
  :new.LEAVE_NAME,
  :new.LEAVE_STATUS,
  user);

END;

This sample code working fine in Oracle SQL.Wrong way i applied Mysql,oralce sql concepts.Thanks to all.

Saro
  • 1
  • 1