Trigger FOR DELETE Operation

when we want to create a trigger for delete operation on any table we can do it as shown below

and by useing "FROM deleted" as we did in our example ("SELECT stor_id FROM deleted") we can access the ID of record which just deleted and trigger got fired.

CREATE TABLE storesmaster(

stor_id char(4) NOT NULL,

stor_name varchar(40) NULL,

stor_address varchar(40) NULL,

city varchar(20) NULL,

)

GO

insert storesmaster values('1','B','567 Ave.','Tustin')

insert storesmaster values('2','N','577 St.', 'Los Gatos')

insert storesmaster values('3','T','679 St.', 'Portland')

insert storesmaster values('4','F','89 St.', 'Fremont')

GO

CREATE TABLE discounts(

discounttype varchar(40) NOT NULL,

stor_id char(4) NULL ,

lowqty smallint NULL,

highqty smallint NULL,

discount dec(4,2) NOT NULL

)

GO

insert discounts values('Initial Customer', NULL, NULL, NULL, 10.5)

insert discounts values('Volume Discount', NULL, 100, 1000, 6.7)

insert discounts values('Customer Discount', '8042', NULL, NULL, 5.0)

GO

CREATE TRIGGER myTrigger ON stores

FOR DELETE

AS

DECLARE @intRowCount int

SELECT @intRowCount = @@RowCount

IF @intRowCount > 0

BEGIN

DELETE sales

WHERE stor_id IN (SELECT stor_id FROM deleted)

DELETE discounts

WHERE stor_id IN (SELECT stor_id FROM deleted)

END

GO

How to find nth highest salary from Employee table in SQL Server?

SELECT TOP 1 salary FROM (SELECT DISTINCT TOP n salary FROM employee ORDER BY salary DESC) a ORDER BY salary

What is a Subquery ?

A Subquery is a normal T-SQL query that is nested inside another query. They are created using parentheses when you have a SELECT statement that serve as the basis for the either part of the data or the condition in another query.
Subqueries are generally used to fill one of couple of needs -

1. Break a query up into a series of a logical steps.
2. Provide a listing to be the target of a WHERE clause together with [IN|ESISTS|ANY|ALL].
3. TO provide a lookup driven by each individual record in a parent query.
Eg.
SELECT SelectList FROM SomeTable WHERE SomeColumn = (SELECT SingleColumn FROM SomeTable WHERE Condition that results in only one row returned)

What is the DEADLOCK ?

A deadlock is a situation in which two transactions conflict with each other and the only resolution is to cancel one transaction.

How to find out column names and their datatypes in a given table using a query in SQL Server?

Using the table tablename, write the query as follows -
Select * from information_schema.columns where table_name = tablename