SQL Triggers examples

After Trigger, Instead of Trigger with Examples

Triggers are special type of stored procedure that automatically execute when a DDL or DML statement associated with the trigger is executed. DML Triggers are used to evaluate data after data manipulation using DML statements. We have two types of DML triggers.

Types of DML Triggers

1.           After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it.

Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

2.           Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.

Example :If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

Example

1.   -- First create table Employee_Demo

2.  CREATE TABLE Employee_Demo

3.  (

4.   Emp_ID int identity,

5.   Emp_Name varchar(55),

6.   Emp_Sal decimal (10,2)

7.  )

8.  -- Now Insert records

9.  Insert into Employee_Demo values ('Amit',1000);

10.Insert into Employee_Demo values ('Mohan',1200);

11.Insert into Employee_Demo values ('Avin',1100);

12.Insert into Employee_Demo values ('Manoj',1300);

13.Insert into Employee_Demo values ('Riyaz',1400);

14.--Now create table Employee_Demo_Audit for logging/backup purpose of table Employee_Demo create table Employee_Demo_Audit

15.(

16. Emp_ID int,

17. Emp_Name varchar(55),

18. Emp_Sal decimal(10,2),

19. Audit_Action varchar(100),

20. Audit_Timestamp datetime

21.)

Now I am going to explain the use of After Trigger using Insert, Update, Delete statement with example

1.           After Insert Trigger

1.   -- Create trigger on table Employee_Demo for Insert statement

2.  CREATE TRIGGER trgAfterInsert on Employee_Demo

3.  FOR INSERT

4.  AS declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);

5.  select @empid=i.Emp_ID from inserted i;

6.  select @empname=i.Emp_Name from inserted i;

7.  select @empsal=i.Emp_Sal from inserted i;

8.  set @audit_action='Inserted Record -- After Insert Trigger.'; insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

9.  values (@empid,@empname,@empsal,@audit_action,getdate());

10.PRINT 'AFTER INSERT trigger fired.'

11.--Output will be

12. --Now try to insert data in Employee_Demo table

13.insert into Employee_Demo(Emp_Name,Emp_Sal)values ('Shailu',1000);

14.--Output will be

15. --now select data from both the tables to see trigger action

16.select * from Employee_Demo

17.select * from Employee_Demo_Audit

18.--Output will be

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can trace a insert activity on a table using trigger.

2.           After Update Trigger

1.   -- Create trigger on table Employee_Demo for Update statement

2.  CREATE TRIGGER trgAfterUpdate ON dbo.Employee_Demo

3.  FOR UPDATE

4.  AS

5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100);

6.  select @empid=i.Emp_ID from inserted i;

7.  select @empname=i.Emp_Name from inserted i;

8.  select @empsal=i.Emp_Sal from inserted i; if update(Emp_Name)

9.   set @audit_action='Update Record --- After Update Trigger.';

10.if update (Emp_Sal)

11. set @audit_action='Update Record --- After Update Trigger.';

12.insert intoEmployee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

13.values (@empid,@empname,@empsal,@audit_action,getdate());

14.PRINT 'AFTER UPDATE trigger fired.'

15.--Output will be

16. --Now try to upadte data in Employee_Demo table

17.update Employee_Demo set Emp_Name='Pawan' Where Emp_ID =6;

18.--Output will be

19. --now select data from both the tables to see trigger action

20.select * from Employee_Demo

21.select * from Employee_Demo_Audit

22.--Output will be

Trigger have inserted the new record to Employee_Demo_Audit table for update statement. In this way we can trace a update activity on a table using trigger.

3.           After Delete Trigger

1.   -- Create trigger on table Employee_Demo for Delete statement

2.  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo

3.  FOR DELETE

4.  AS

5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;

6.  select @empname=d.Emp_Name from deleted d;

7.  select @empsal=d.Emp_Sal from deleted d;

8.  select @audit_action='Deleted -- After Delete Trigger.';

9.  insert into Employee_Demo_Audit (Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

10.values (@empid,@empname,@empsal,@audit_action,getdate());

11.PRINT 'AFTER DELETE TRIGGER fired.'

12.--Output will be

13. --Now try to delete data in Employee_Demo table

14.DELETE FROM Employee_Demo where emp_id = 5

15.--Output will be

16. --now select data from both the tables to see trigger action

17.select * from Employee_Demo

18.select * from Employee_Demo_Audit

19.--Output will be

Trigger have inserted the new record to Employee_Demo_Audit table for delete statement. In this way we can trace a delete activity on a table using trigger.

Now I am going to explain the use of Instead of Trigger using Insert, Update, Delete statement with example

1.           Instead of Insert Trigger

1.   -- Create trigger on table Employee_Demo for Insert statement

2.  CREATE TRIGGER trgInsteadOfInsert ON dbo.Employee_Demo

3.  INSTEAD OF Insert

4.  AS

5.  declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);

6.  select @emp_id=i.Emp_ID from inserted i;

7.  select @emp_name=i.Emp_Name from inserted i;

8.  select @emp_sal=i.Emp_Sal from inserted i;

9.  SET @audit_action='Inserted Record -- Instead Of Insert Trigger.';

10.BEGIN

11. BEGIN TRAN

12. SET NOCOUNT ON

13. if(@emp_sal>=1000)

14. begin

15. RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end

16. else begin Insert into Employee_Demo (Emp_Name,Emp_Sal) values (@emp_name,@emp_sal); Insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@@identity,@emp_name,@emp_sal,@audit_action,getdate());

17. COMMIT;

18. PRINT 'Record Inserted -- Instead Of Insert Trigger.'

19.END

20.--Output will be

21. --Now try to insert data in Employee_Demo table

22.insert into Employee_Demo values ('Shailu',1300)

23.insert into Employee_Demo values ('Shailu',900) -- It will raise error since we are checking salary >=1000

24.--Outputs will be

25. --now select data from both the tables to see trigger action

26.select * from Employee_Demo

27.select * from Employee_Demo_Audit

28.--Output will be

Trigger have inserted the new record to Employee_Demo_Audit table for insert statement. In this way we can apply business validation on the data to be inserted using Instead of trigger and can also trace a insert activity on a table.

2.           Instead of Update Trigger

1.   -- Create trigger on table Employee_Demo for Update statement

2.  CREATE TRIGGER trgInsteadOfUpdate ON dbo.Employee_Demo

3.  INSTEAD OF Update

4.  AS

5.  declare @emp_id int, @emp_name varchar(55), @emp_sal decimal(10,2), @audit_action varchar(100);

6.  select @emp_id=i.Emp_ID from inserted i;

7.  select @emp_name=i.Emp_Name from inserted i;

8.  select @emp_sal=i.Emp_Sal from inserted i;

9.  BEGIN

10. BEGIN TRAN

11.if(@emp_sal>=1000)

12. begin

13. RAISERROR('Cannot Insert where salary < 1000',16,1); ROLLBACK; end

14. else begin

15. insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp) values(@emp_id,@emp_name,@emp_sal,@audit_action,getdate());

16. COMMIT;

17. PRINT 'Record Updated -- Instead Of Update Trigger.'; END

18.--Output will be

19. --Now try to upadte data in Employee_Demo table

20.update Employee_Demo set Emp_Sal = '1400' where emp_id = 6

21.update Employee_Demo set Emp_Sal = '900' where emp_id = 6

22.--Output will be

23. --now select data from both the tables to see trigger action

24.select * from Employee_Demo

25.select * from Employee_Demo_Audit

26.--Output will be

Trigger have inserted the updated record to Employee_Demo_Audit table for update statement. In this way we can apply business validation on the data to be updated using Instead of trigger and can also trace a update activity on a table.

3.           Instead of Delete Trigger

1.   -- Create trigger on table Employee_Demo for Delete statement

2.  CREATE TRIGGER trgAfterDelete ON dbo.Employee_Demo

3.  INSTEAD OF DELETE

4.  AS

5.  declare @empid int, @empname varchar(55), @empsal decimal(10,2), @audit_action varchar(100); select @empid=d.Emp_ID FROM deleted d;

6.  select @empname=d.Emp_Name from deleted d;

7.  select @empsal=d.Emp_Sal from deleted d;

8.  BEGIN TRAN if(@empsal>1200) begin

9.   RAISERROR('Cannot delete where salary > 1200',16,1);

10. ROLLBACK;

11. end

12. else begin

13. delete from Employee_Demo where Emp_ID=@empid;

14. COMMIT;

15. insert into Employee_Demo_Audit(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

16. values(@empid,@empname,@empsal,'Deleted -- Instead Of Delete Trigger.',getdate());

17. PRINT 'Record Deleted -- Instead Of Delete Trigger.' end END

18.--Output will be

19. --Now try to delete data in Employee_Demo table

20.DELETE FROM Employee_Demo where emp_id = 1

21.DELETE FROM Employee_Demo where emp_id = 3

22.--Output will be

23. --now select data from both the tables to see trigger action

24.select * from Employee_Demo

25.select * from Employee_Demo_Audit

26.--Output will be

Trigger have inserted the deleted record to Employee_Demo_Audit table for delete statement. In this way we can apply business validation on the data to be deleted using Instead of trigger and can also trace a delete activity on a table.

 Instead of trigger another example:

If you have created a View in SQL which is based on a single table – the DML operations you perform on the view are automatically propagated to the base table.

However, when you have joined multiple tables to create a view you will run into below error if you execute a DML statement against the view:

Msg 4405, Level 16, State 1, Line 1
View or function 'ViewName' is not updatable because the modification affects
multiple base tables.

To avoid this error and make a view modifiable you need to create Triggers on the view. These triggers will be used to ‘pass’ the changes to base tables.

You need to create a trigger on a view for all operations you need to perform. For example, if you need to perform INSERT operations on a view you need to create a INSTEAD OF Trigger for ‘passing’ the changes to base tables. If you also need to perform a UPDATE/DELETE operation, you also need to create additional INSTEAD OF Triggers for UPDATE/DELETE.

For example, let’s consider following view definition:
– © 2013 –
CREATE TABLE [dbo].[Table1]
( [ID] [INT] NULL,
[Name] [VARCHAR](20) NULL
)

CREATE TABLE [dbo].[Table2]
( [ID1] [INT] NULL,
[Name1] [VARCHAR](20) NULL
)

CREATE View [dbo].[View1]
AS
SELECTTable1.ID, Table1.Name, Table2.Name1
FROM Table1
INNER JOIN Table2
ON Table2.ID1 = Table1.ID

Now, if you try to insert to [View1], you will run into above error. To enable INSERTs on [View1], we need to create INSTEAD OF Trigger as below:

– © 2013 –
CREATE TRIGGER [dbo].[Trig_Insert_Employee]
ON [dbo].[View1]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO Table1
SELECT I.ID, I.Name
FROM INSERTED I

INSERT INTO Table2
SELECT I.ID, I.Name1
FROM INSERTED I
END

That’s all folks. Now, you can execute INSERT statement against the view and it will INSERT the data to the base tables.

INSERT INTO View1 Values (1,'Gandalf','The Grey')

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

Verify the base tables:

 

Triggers types

A trigger is an exceptional sort of stored procedure which functions when we try to amend the data in a table like inserting, deleting or updating data. It is a database object, executed automatically and is bound to a table. Fundamentally, triggers are classified into two types mainly-

• Instead of Trigger
• After Trigger

We know how we can insert, delete or update operations aligned with excessively intricate views to support natively through ‘Instead of Trigger’. In other words, we can use this trigger as an interceptor for an action attempted on our table.

Instead of Trigger is an important element which is emphasized in almost every SQL course. Here, we will discuss the situation where we want to make a table column which can auto generate the customized sequence. We can see an example of the same below-

Here, we don’t have to misunderstand the id column above to be an identity column. This column is of character data type . All we want is to auto generate this column as it is displayed in the figure above.

Usage

‘Instead of Trigger’ can help us to easily solve the situation above. In ‘Instead of Trigger ‘ we insert the data into the virtual tables prior to checking the constraints. As far as ‘After Trigger’ constraints are concerned, they are checked in the first place. Data is then inserted into the virtual tables ( inserted and deleted tables).

We can consider the code mentioned below for better understanding-

CREATE TABLE [dbo].[Employee1](
[id] CHAR(10) PRIMARY KEY,
[name] VARCHAR(50)
)
GO
INSERT INTO [dbo].[Employee1] VALUES('a1','John')
GO

Now, for an id column, we need to automatically generate a2, a3, a4….. For this, we can write a code in an insert trigger. Therefore, everytime the trigger command occurs, the trigger fires and the next number is generated.

Let us consider the command mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')

Now we will enter the data in the column (name). By doing so, we will be entering NULL values in the column (id). We have a primary key column in the (id) column. In a primary key, there is no permission for NULL. Therefore, the Primary Key constraint is violated.

In case, we make use of ‘After Trigger’, then constraints are checked prior to inserting the data into the implied table. The primary key constraint is violated in this case. Therefore, we can’t put data into virtual table. As a result, we will not find the trigger firing. On the other hand, on making use of ‘Instead of Trigger’, data is inserted into the virtual table prior to the constraint check.

Therefore, our virtual (inserted) table will be as-

Instead of Trigger’s code will be now fired. It is written as-

--Instead of Trigger
CREATE TRIGGER AutoIncrement_Trigger ON [dbo].[Employee1]
instead OF INSERT AS
BEGIN
DECLARE @ch CHAR
DECLARE @num INT
SELECT @num=SUBSTRING(MAX(id),2,1) FROM [dbo].[Employee1]
SELECT @ch=SUBSTRING(MAX(id),1,1) FROM [dbo].[Employee1]
IF @num=9
BEGIN
SET @num=0
SET @ch= CHAR( ( 1 + ASCII(@ch) ))
END
INSERT INTO [dbo].[Employee1] (id,name) SELECT (@ch+CONVERT(VARCHAR(9),(@num+1))),inserted.name FROM inserted
END

Explanation of the Code Above

The trigger’s code gets the greatest value from the id column. This is done when we use MAX(id)function, parse the integer data and the character. Now with the use of substring function, put it in @ch and @num variables respectively.

When @num turns 9 then @num is reset to 0. The character is then increased to the next character.

For instance, if @ch= 'a' then

ASCII('a')=97

@ch=CHAR(1+97)=CHAR(98)='b'

Soon after, @num raises by 1 and gets coupled with the @ch variable. Then, it will be placed into the dbo.employee1 table.

Now we can run the commands mentioned under-

INSERT INTO [dbo].[Employee1] (name) VALUES('Aslam')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alan')
INSERT INTO [dbo].[Employee1] (name) VALUES('Mike')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rahul')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vikas')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vijay')
INSERT INTO [dbo].[Employee1] (name) VALUES('Vineet')
INSERT INTO [dbo].[Employee1] (name) VALUES('Rajat')
INSERT INTO [dbo].[Employee1] (name) VALUES('Alice')
SELECT * FROM [dbo].[Employee1]

With the information provided in the piece of writing above, we know how important is Instead of Trigger in SQL. It provides a clear approach to modify views that we cannot change directly through DML statements (INSERT, UPDATE, and DELETE).

NOTE: It is always a good idea to design your system such a way that you do not need trigger to use. You can include the logic of trigger in your code as well in your procedure and avoid the usage of the trigger. Triggers are very difficult to debug as well adds lots of overhead to the system. There are many performance problems due to poor implementation of the trigger. This post is just created for demonstration of how triggers can be used in special cases.

 

Difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT

Difference between @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT

 

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

 

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

 

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

 

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

 

For live example:

CREATE TABLE TA (

   A_id  int IDENTITY(1,1)PRIMARY KEY,

   A_name varchar(20) NOT NULL)

 

INSERT TA   VALUES ('Ashok')

INSERT TA   VALUES ('Kumar')

INSERT TA   VALUES ('Ajay')

 

CREATE TABLE TB (

   B_id  int IDENTITY(100,5)PRIMARY KEY,

   B_name varchar(20) NULL)

 

INSERT TB (B_name)   VALUES ('HCL')

INSERT TB (B_name)   VALUES ('PRINT')

INSERT TB (B_name)   VALUES ('ZAN')

 

 

/*Create the trigger that inserts a row in table TB

when a row is inserted in table TA.*/

CREATE TRIGGER ATrigg

ON TA

FOR INSERT AS

   BEGIN

   INSERT TB VALUES ('')

   END

 

/*FIRE the trigger and determine what identity values you obtain

with the @@IDENTITY and SCOPE_IDENTITY functions.*/

INSERT TA VALUES ('AK')

 

SELECT * FROM TA

SELECT * FROM TB

 

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

GO

SELECT @@IDENTITY AS [@@IDENTITY]

GO

SELECT IDENT_CURRENT( 'TA') AS [IDENT_CURRENT]

 

Conclusion:

So all three @@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT returns the last inserted identity value from the table where identity column is defined.

@@IDENTITY returns last inserted identity value in current session.

SCOPE_IDENTITY returns last inserted identity value in current session considering with current scope(refer trigger example).

 

IDENT_CURRENT returns last inserted identity value from associated table and did not depend on session and scope.

 

 

Software Design Principles

Different Types of Software Design Principles

Software design principles are a set of guidelines that helps developers to make a good system design. The most important principle is SOLID principle. The short abbreviation is  known as

SOLID

The key software design principles are as:

It is combination of five basic designing principles.

1.           Single Responsibility Principle (SRP)

This principle states that there should never be more than one reason for a class to change. This means that you should design your classes in such a way that each class should have a single purpose means single responsibility.

2.           Open/Closed Principle (OCP)

This principle states that software entities (classes, modules, functions, etc.) should be open for extension but closed for modification. The "closed" part of the rule states that once a module has been developed and tested, the code should only be changed to correct bugs. The "open" part says that you should be able to extend existing code in order to introduce new functionality.

3.           Liscov Substitution Principle (LSP)

The principle defines that objects of a superclass shall be replaceable with objects of its subclasses without breaking the application. That requires the objects of your subclasses to behave in the same way as the objects of your superclass.

4.           Interface Segregation Principle (ISP)

This principle states that Clients should not be forced to depend upon interfaces that they don’t use. This means the number of members in the interface that is visible to the dependent class should be minimized.

5.           Dependency Inversion Principle (DIP)

The Dependency Inversion Principle states that:

1.     High level modules should not depend upon low level modules. Both should depend upon abstractions.

2.     Abstractions should not depend upon details. Details should depend upon abstractions.

It helps us to develop loosely couple code by ensuring that high-level modules depend on abstractions rather than concrete implementations of lower-level modules. The Dependency Injection pattern is an implementation of this principle

Call Server Side method from JavaScript

This query is asked many times on asp.net forums,

How to call Server Side methods or functions using JavaScript in ASP.Net?
So I am providing the best solution for this important question as below.

Calling server side methods using JavaScript in ASP.Net

There is two ways for this solution.

1.     Using PageMethods through AJAX.

2.     IPostBackEventHandler .

I am explaining the same using ASP.Net AJAX PageMethods.

ASP.Net AJAX ScriptManager allows you to call Server Side ASP.net methods from client side without any PostBack using PageMethods. Actually it is an AJAX call to the server but it allows us to call the method or function defined server side.

Enabling PageMethods

The first thing you need to do is add a ASP.Net AJAX ScriptManager to the page and set its EnablePageMethods property to true as shown below

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">

</asp:ScriptManager>

HTML Markup

<form id="form1" runat="server">

<asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">

</asp:ScriptManager>

<div>

Your Company :

<asp:TextBox ID="txtName" runat="server" ></asp:TextBox>

<input id="btnGetTime" type="button" value="Show Current Time" onclick="ShowCurrentTime()"/>

</div>

</form>

As you noticed above I have added a textbox when user can enter his name and a HTML button that calls a JavaScript method to get the Current Time.

Client Side Methods

<script type="text/javascript">

function ShowCurrentTime() {

PageMethods.GetCurrentTime(document.getElementById("<%=txtUserName.ClientID%>").value, OnSuccess);

}

function OnSuccess(response, userContext, methodName) {

alert(response);

}

</script>

Above the ShowCurrentTime method makes an AJAX call to the server using ASP.Net AJAX ScriptManager PageMethods and executes the GetCurrentTime method which accepts the Company Name and returns a string value.

Server Side Methods

C#

[System.Web.Services.WebMethod]

public static string GetCurrentTime(string Company)

{

return"Ashok" + Company+ Environment.NewLine + "The Current Time is: "

+ DateTime.Now.ToString();

}

The above method simply returns a greeting message to the user along with the current server time. An important thing to note is that the method is declared as static (C#) and also it is declared as Web Method unless you do this you won’t be able to call the methods using ASP.Net ScriptManager PageMethods.

Troubleshooting: ‘PageMethods Is 'Undefined'’ error

1.       Try setting EnablePageMethods="true" in the script manager tag

<asp:ScriptManager ID="ScriptManager1" runat="server"  EnablePageMethods="true"/>

2. Page methods needs to be static in code behind.

If anyone has doubt then please let me know.